SQL String Functions > TO_DATE Function

SQL > SQL String Functions > TO_DATE Function

The TO_DATE function is used in Oracle to convert a string to a date. The syntax of this function is as follows:

TO_DATE ( String, [Format], [Optional Setting] )

The most important parameter is [Format]. Valid [Format] values are as follows:

Format	Explanation
AD A.D.	AD indicator to use in conjunction with the year
AM A.M. PM P.M.	Meridian indicator
BC B.C.	BC indicator to use in conjunction with the year
D	Day of week (1-7)
DAY	Name of day
DD	Day of month (1-31)
DDD	Day of year (1-366)
DY	Abbreviated name of day
HH	Hour of day (1-12)
HH24	Hour of day (0-23)
MI	Minutes (0-59)
MM	Month (01-12)
MON	Abbreviated name of month
MONTH	Name of month
RM	Month in Roman Numerals (I - XII)
RR	Accepts a 2-digit input, and returns a 4-digit year. A value between '00' and '49' returns the year in the same century. A value between '50' and '99' returns a year in the previous century.
RRRR	Accepts a 2-digit input or a 4-digit input, and returns a 4-digit year. For 4-digit input, the same value is returned. For 2 digit input, a value between '00' and '49' returns the year in the same century, and a value between '50' and '99' returns a year in the previous century.
SS	Second (0-59)
SSSSS	Seconds past midnight (0-86399)
Y	Accepts a 1-digit input, and returns a 4-digit year in that decade.
YY	Accepts a 2-digit input, and returns a 4-digit year in that century.
YYY	Accepts a 3-digit input, and returns a 4-digit year in that millennium.
YYYY SYYYY	Accepts a 4-digit input, and returns a 4-digits year.

Below are some examples on using the TO_DATE function. For clarity, the results are expressed in the 'YYYY MM DD HH24:MI:SS' format (Year Month Date Hour:Minute:Second, where Hour has a value between 0 and 23):

Example 1

SELECT TO_DATE('20100105', 'YYYYMMDD') FROM DUAL;

Result:

2010 01 05 00:00:00

Example 2

SELECT TO_DATE('1999-JAN-05', 'YYYY-MON-DD') FROM DUAL;

Result:

1999 01 05 00:00:00

Example 3

SELECT TO_DATE('2005-12-12 03600', 'YYYY-MM-DD SSSSS') FROM DUAL;

Result:

2005 12 12 01:00:00

3600 seconds equals to 1 hour.

Example 4

SELECT TO_DATE('2005 120 05400', 'YYYY DDD SSSSS') FROM DUAL;

Result:

2005 04 30 01:30:00

April 30th is the 120th day in 2005. 5400 seconds equals to 1 hour and 30 minutes.

Example 5

SELECT TO_DATE('99-JAN-05', 'YY-MON-DD') FROM DUAL;

Result:

2099 01 05 00:00:00

The 'YY' format converts the year to the current century.

Example 6

SELECT TO_DATE('99-JAN-05', 'RR-MON-DD') FROM DUAL;

Result:

1999 01 05 00:00:00

The 'RR' logic converts '99' to the previous century, hence the result is 1999.