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:

FormatExplanation
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
DDay of week (1-7)
DAYName of day
DDDay of month (1-31)
DDDDay of year (1-366)
DYAbbreviated name of day
HHHour of day (1-12)
HH24Hour of day (0-23)
MIMinutes (0-59)
MMMonth (01-12)
MONAbbreviated name of month
MONTHName of month
RMMonth in Roman Numerals (I - XII)
RRAccepts 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.
RRRRAccepts 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.
SSSecond (0-59)
SSSSSSeconds past midnight (0-86399)
YAccepts a 1-digit input, and returns a 4-digit year in that decade.
YYAccepts a 2-digit input, and returns a 4-digit year in that century.
YYYAccepts 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.

Next: SQL Date Functions




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact