to_date function

to_date function converts a string to a date. The syntax for the to_date function is:

to_date( string1, [ format_mask ], [ nls_language ] )

Parameter	Description
string1	string to be converted
format_mask	optional format
nls_language	optional nls language

If the format is missing, the date must be in the default database format (usually DD-MON-YYYY or DD-MON-YY). The NLS_DATE_FORMAT database parameter specifies the default date format.


SQL> SELECT TO_DATE('04-JUL-2012'), TO_DATE('04-JUL-12') FROM dual;

TO_DATE(' TO_DATE('
--------- ---------
04-JUL-12 04-JUL-12

SQL>

Format parameters for converting a date value to string.

Parameter	Description
CC	Two-digit century.
SCC	Two-digit century with a negative sign (-) for B.C.
Q	One-digit quarter of the year. (1, 2, 3, 4; JAN-MAR = 1)
YYYY	All 4 digits of the year.
IYYY	All 4 digits of the ISO year.
RRRR	All 4 digits of the rounded year (governed by the present year).
SYYYY	All 4 digits of the year with a negative sign (-) for B.C.
Y,YYY	All 4 digits of the year, with a comma after the first digit.
YYY	Last 3 digits of the year.
IYY	Last 3 digits of the ISO year.
YY	Last 2 digits of the year.
IY	Last 2 digits of the ISO year.
RR	Last two digits of the rounded year, which depend on the present year.
Y	Last digit of the year.
I	Last digit of the ISO year.
YEAR	Name of the year in uppercase.
Year	Name of the year with the first letter in uppercase.
MM	Two-digit month of the year. (01-12; JAN = 01)
MONTH	Full name of the month in uppercase, right-padded with spaces to a total length of nine characters.
Month	Full name of the month with first letter in uppercase, right-padded with spaces to a total length of nine characters.
MON	First three letters of the name of the month in uppercase.
Mon	First three letters of the name of the month with the first letter in uppercase.
RM	Roman numeral month. (I-XII; JAN = I)
WW	Two-digit week of the year. (1-53)
IW	Two-digit ISO week of the year.(1-52 or 1-53)
W	One-digit week of the month.(1-5)
DDD	Three-digit day of the year. (1-366)
DD	Two-digit day of the month.(1-31)
D	One-digit day of the week.(1-7)
DAY	Full name of the day in uppercase.
Day	Full name of the day with the first letter in uppercase.
DY	First three letters of the name of the day in uppercase.
Dy	First three letters of the name of the day with the first letter in uppercase.
J	Julian day-the number of days that have passed since January 1, 4713 B.C.
HH24	Two-digit hour in 24-hour format. (0-23)
HH12	Hour of day.(1-12)
HH	Two-digit hour in 12-hour format.(1-12)
MI	Two-digit minute. (0-59)
SS	Two-digit second.(0-59)
FF[1..9]	Fractional seconds.
SSSSS	Number of seconds past 12 A.M. (0-86399)
MS	Millisecond (millionths of a second).
CS	Centisecond (hundredths of a second).
-/,.;:"text"	Characters that allow you to separate the aspects of a date and time.
AM or PM A.M. or P.M. AD or BC	AM or PM A.M. or P.M. AD or BC
SP	Number is spelled out
SPTH	Combination of SP and TH
EE	Full era name
E	Abbreviated era name
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.
TZD	Time zone with daylight savings information.

The following table illustrates some TO_CHAR date formatting:

Format	Output
dd Month yyyy	05 March 2012
dd month YY	05 march 12
dd Mon	05 Mar
dd RM yyyy	05 III 2012
Day Mon yyyy	Sunday Mar 2012
Day fmMonth dd, yyyy	Sunday March 5, 2012
Mon ddsp yyyy	Mar five 2012
ddMon yy hh24:mi:ss	05Mar 12 00:00:00
MONTH DD, YYYY	FEBRUARY 05, 2012
MM/DD/YYYY	02/05/2012
MM-DD-YYYY	02-05-2012
DD/MM/YYYY	05/02/2012
DAY MON, YY AD	MONDAY FEB, 12 AD
DDSPTH "of" MONTH, YEAR A.D.	FIFTH of FEBRUARY, TWENTY TWELVE A.D.
CC, SCC	20, 20
Q	1
YYYY	2012
IYYY	2012
RRRR	2012
SYYYY	2012
Y,YYY	2,012
YYY	012
IYY	012
YY	12
IY	12
RR	12
Y	2
I	2
YEAR	TWENTY TWELVE
Year	Twenty Twelve
MM	02
MONTH	FEBRUARY
Month	February
MON	FEB
Mon	Feb
RM	II
WW	02
IW	02
W	2
DDD	035
DD	06
DAY	MONDAY,
Day	Monday
DY	MON
Dy	Mon
J	2439999
ddTH	05th
DDTH	05TH
ddSP	five
DDSP	FIVE
DDSPTH	FIFTH
HH24:MI:SS	19:12:36
HH.MI.SS AM	7.12.36 PM

Example:


SQL> select to_date('2012/07/09', 'yyyy/mm/dd') from dual;

TO_DATE('
---------
09-JUL-12

SQL> select to_date('070912', 'MMDDYY') from dual;

TO_DATE('
---------
09-JUL-12

SQL> select to_date('20120315', 'yyyymmdd')     from dual;

TO_DATE('
---------
15-MAR-12

SQL> SELECT TO_DATE('July 4, 2012', 'MONTH DD, YYYY') FROM dual;

TO_DATE('
---------
04-JUL-12

SQL> SELECT TO_DATE('7.4.12', 'MM.DD.YY') FROM dual;

TO_DATE('
---------
04-JUL-12

SQL>

Home »
Oracle »
Conversion Functions »

to_date:

to_date function
to_date and insert statement