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, 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.
TZD Daylight savings information. e.g, ‘PST’
TZH Time Zone Hour
TZM Time Zone Minute
TZR Time Zone Region

ORACLE TO_DATE Function Example

-- 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

Leave a Comment