Oracle To_date() is an SQL function which will convert a string to date. In SQL Server we could use SQL CONVERT function to convert a string to date.
ORACLE TO_DATE Function Syntax
The Oracle to_date() function’s syntax could be one of the following:
to_date(string)
to_date(string, date_format)
to_date(string, date_format, nls_language)
string: which will be converted to date.
date_format: the format we will use to convert string to date.
The date_format mask parameters are listed as following: (They could be used in combinations)
| Parameter | Explanation |
|---|---|
| YEAR | Year in characters |
| YYYY | 4-digit year |
| YY | 2-digit year |
| IYYY | 4-digit ISO year |
| IY | 2-digit ISO year |
| RRRR | Take a 2-digit year as input and a 4-digit year as output. 0-49 will return a 20xx year. 50-99 will return a 19xx year. |
| Q | Quarter (1, 2, 3, 4) |
| MM | Month (01, 02, 03, … 12) |
| MON | Abbreviated name of month (Jan, Feb, Mar, … Dec) |
| MONTH | Name of month (January, February, March, … December) |
| RM | Roman number of month (I, II, … XII) |
| WW | Week number (1 – 53) |
| W | Week number of the month (1 – 5) |
| IW | Week number of the ISO year (1 – 52 or 1 – 53) |
| D | Day of week (1 – 7) |
| Day | Day of week in characters (Monday, Tuesday, … Sunday) |
| DD | Day of month (1 – 31) |
| DDD | Day of year (1 – 366) |
| DY | Day of week in short characters (Mon, Tue, … Sun) |
| J | Julian day; the number of days since January 1, 4712 BC. |
| HH, HH12 | Hour number of the day (1 – 12) |
| HH24 | Hour number of the day with 24 hour format (0 – 23) |
| MI | Minute number (0 – 59) |
| SS | Second number (0 – 59) |
| SSSSS | Number of seconds this day(0 – 86399) |
| FF | Fractional seconds. |
| AM, PM | AM or PM |
| AD, A.D | AD |
| BC, B.C | BC |
| TZD | Daylight savings information. e.g, ‘PST’ |
| TZH | Time Zone Hour |
| TZM | Time Zone Minute |
| TZR | Time Zone Region |
ORACLE TO_DATE Function Example
to_date('13-JAN-2010')
-- return a date value of January 13, 2010
to_date('2011-03-24', 'yyyy-mm-dd')
-- return a date value of March 24, 2011
to_date('20110523', 'yyyymmdd')
-- return a date value of May 23, 2011