ORACLE TO_DATE

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>