Turn: Summary of some usages of To_char and to_date

Original: http://blog.itpub.net/13994314/viewspace-1006120/

For starters, date processing has always been the focus of the problem. Just read a case today, will be the Oracle date processing errors, the key reason is that the use of To_char and to_date is not clear, in fact, these two things are particularly easy to confuse, for this reason, Introduce their differences again.

To char is to convert a date or number to a string
To date is the conversion of a string to a date type in the database
? Conversion functions
? To_char
Working with numbers using the To_char function
To_char (number, ' format ')
To_char (Salary, ' $99,999.99 ');
Working with dates using the To_char function
To_char (date, ' format ');
? To_number
Convert characters to numbers using the To_number function
To_number (char[, ' format ')
? To_date
Convert a character to a date using the To_date function
To_date (char[, ' format ')

To_date:

spl> SELECT * from emp
where dates
Between
To_date (' 2007-06-12 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')
and
To_date (' 2007-06-12 10:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

Oracle uses 0 as the default value when omitting input parameters corresponding to HH, MI, and SS. If the date data entered ignores the time portion, Oracle will set the time, minute, and second portions to 0, which means rounding to day.

Similarly, when the DD parameter is omitted, Oracle takes 1 as the default value for the day, which means that it is rounded to the month.

However, do not be confused by this "inertia", if the mm parameter is ignored, Oracle does not take the whole year, rounding to the current month.

Attention:

1. When using Oracle's To_date function for date conversion, it is possible to intuitively convert in the format of "Yyyy-mm-dd HH:mm:ss", but in Oracle it causes an error: "ORA 01810 format code two times". such as: Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mm:ss ') from dual;
The reason is that SQL is case-insensitive, mm and mm are considered the same format code, so Oracle's SQL uses MI instead of minutes. Select To_date (' 2005-01-01 13:14:20 ', ' yyyy-mm-dd HH24:mi:ss ') from dual;
2. Another 24-hour display to use HH24
Select To_char (sysdate, ' Yyyy-mm-dd HH24:mi:ss ') from Dual;//mi is minutes
Select To_char (sysdate, ' yyyy-mm-dd HH24:mm:ss ') displays the month from dual;//mm




To_char:

1 ,

To_char (nchar | clob | nclob)

To accept strings of nchar, NVARCHAR2, CLOB, and NCLOB types as their own arguments, convert them to the character set of the database. Take a look at the following example:

This is the case of the national character set:

This is the case of CLOB:

09:59:03 sql> CREATE TABLE T (a clob); Table created. 09:59:15 sql> insert INTO t values (' aaaaaaaaaaaaaaaaaaaaaaaaaa '); 1 row created. 09:59:31 sql> select * from T; A -------------------------------------------------------------------------------- Aaaaaaaaaaaaaaaaaaaaaaaaaaa 10:00:03 sql> Select To_char (a) from T To_char (A) --------------------------------------------------------------------------------------------------------------- ------- Aaaaaaaaaaaaaaaaaaaaaaaaaaa

2.

To_char (date [, Fmt [, ' Nlsparam '])

It is a string that converts a date datetime into a specified format, FMT. About Nlsparam basically can be used to determine the name of the returned date and abbreviations, such as the form, such as your database language if it is English, then the returned month is likely to be similar to the "may", and if your database language is Chinese, then the returned month may be similar to the " May ". Nlsparam is used to change these displays, such as assuming your database language is Chinese, and you like to turn it into an English expression, you can set this: nls_date_language= ' 中文版 '. 99% It is not necessary to control this parameter. As the following example:

10:13:29 sql> Select To_char (sysdate, ' yyyy-mm-dd ') today from dual; TODAY ---------- 2007-09-24

Probably the most problematic of all is what the FMT format is, and here's a list to illustrate these:

Elements	Express meaning
- / , . ; : "Test"	These punctuation marks and specific strings will eventually show up, in fact, the function they are starting is to separate, as the following date: 2007-9-16 This meets the "-" to start the day of separation of the role.
AD A.D.	After A.D.
Bc B.C.	Bc
Am a.m..	Morning, morning
Pm the p.m..	Afternoon
D	The first day of the week, Sunday is 1
Day	The day of the week, a total of 9 character size to display, if not so long, the name of the right with a space to fill
Dd	The first day of one months, "1~31"
Ddd	The first day of the year, "1~366"
Dy	The day of the week, but the abbreviation, as Monday may be mon
FF[0..9]	This is for the timestamp type, which indicates how many digits are divided into seconds, followed by a number from 0 to 9 after the FF.
HH	Hours, "1~12"
HH12	Hours, "1~12"
HH24	Hours, "0~23"
MI	Minutes, "0~59"
Mm	month, "01~12"
MON	month, but is the abbreviation of the month, as January may be Jan to indicate
MONTH	month, the name of the month, such as September may be September to represent, altogether with 9 character size to display, if not so long, the name of the right with a space to fill
Q	Season, "1,2,3,4", respectively, represents the spring and autumn
Ss	Seconds, "0~59"
SSSS	Calculates the current number of seconds from midnight at 0 seconds
Ww	The first few weeks of the year, "1~53"
W	The first week of one months, "the"
Y,yyy	Year with a comma in the middle
Year Syear	The year, shown in string, s indicates that BC (BC) uses "-" to denote
YYYY Syyy	A 4-digit year, s indicates that BC (BC) uses "-" to denote
YYY Yy Y	Represents three, two, and a number of the following years, respectively

Turn: Summary of some usages of To_char and to_date