SQL > SQL String Functions > TO_DATE Function

The TO_DATE function is used in Oracle to convert a string to a date.

Syntax

The syntax of this function is as follows:

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

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

 Format  Description 
 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.

[NLS Setting] is used to change the output format based on the NLS Territoy and NLS Language (NLS stands for National Language Support). It is optional and is rarely used.

Examples

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

This page was last updated on October 12, 2024.



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