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