|
SQL > SQL String Functions >
TO_DATE Function
The TO_DATE function is used in Oracle to convert a string to a date.
Key Takeaway: Oracle's TO_DATE function converts a text string into a DATE value using a format mask. The format must match the structure of the input string exactly; common elements include YYYY (year), MM (month), DD (day), HH24 (hour), MI (minute), and SS (second).
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.
Frequently Asked Questions
- What does the Oracle TO_DATE function do?
- TO_DATE converts a character string into an Oracle DATE value using a format mask that describes the structure of the input string, such as 'YYYYMMDD' or 'YYYY-MON-DD'.
- What is the syntax for TO_DATE?
TO_DATE(String, [Format], [NLS Setting]). The Format is mandatory when the string is not in Oracle's default date format. For example: TO_DATE('20100105', 'YYYYMMDD') returns January 5, 2010.- What is the difference between YY and RR year formats?
- YY always interprets a 2-digit year as the current century (e.g., '99' → 2099). RR uses a sliding century window: '00'–'49' maps to the current century, and '50'–'99' maps to the previous century (e.g., '99' → 1999).
- Is TO_DATE available in MySQL and SQL Server?
- TO_DATE is Oracle-specific. MySQL uses
STR_TO_DATE(string, format). SQL Server uses CONVERT(date, string, style) or CAST(string AS date) for similar date conversion.
Next: SQL Date Functions
This page was last updated on March 19, 2026.
Copyright © 2026 1keydata.com All Rights Reserved
Privacy Policy About Contact |