Oracle TO_DATE Function

Oracle TO_DATE function is used to convert string type into date type. Oracle SQL TO_DATE examples and syntax can be found below.

---

Oracle TO_DATE Function Syntax

SELECT TO_DATE (STRING_VALUE, [FORMAT_MASK],[NLS_LANGUAGE])
FROM table_name;

In the above Oracle TO_DATE Function Syntax:

• STRING_VALUE is the value that will be converted into date format.
• FORMAT_MASK is an optional field, if present; it is used to convert value into string.
• NLS_LANGUAGE is also an optional field, if present; it is used to convert value into string.

Table below shows Oracle SQL TO_DATE Function behavior with different parameters while converting string to date:

Parameter	Explanation
AD or A.D	AD indicator
AM, A.M., PM, or P.M.	Meridian indicator
BC or B.C.	BC indicator
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.
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, ‘FF4’.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
I	Last 3, 2, or 1 digit(s) of ISO year.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
IY	Last 3, 2, or 1 digit(s) of ISO year.
IYY	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
J	Julian day; the number of days since January 1, 4712 BC.
MI	Minute (0-59).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
RM	Roman numeral month (I-XII; JAN = I).
RRRR	Accepts a 2-digit year and returns a 4-digit year.A value between 0-49 will return a 20xx year.A value between 50-99 will return a 19xx year.
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
TZD	Daylight savings information. For example, ‘PST’
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
Y	Last 3, 2, or 1 digit(s) of year.
YEAR	Year, spelled out
YY	Last 3, 2, or 1 digit(s) of year.
YYY	Last 3, 2, or 1 digit(s) of year.
YYYY	4-digit year

Let’s have some Oracle TO_DATE Examples.

Note: The output of Oracle TO_DATE Examples may differ for you depending on the passed parameters.

---

Oracle TO_DATE Examples – Convert String to Date

Oracle TO_DATE examples below convert String to Date type.

SELECT TO_DATE('2012/01/09', 'yyyy/mm/dd') "Oracle SQL TO_DATE"
FROM dual;

Above Oracle TO_DATE formats the String to Date data type and returns a date value for January 9, 2012.

Note: We have aliased TO_DATE(‘2012/01/09’, ‘yyyy/mm/dd’) as Oracle SQL TO_DATE.

---

Oracle TO_DATE Examples – Oracle TO_DATE Format

Oracle TO_DATE Format example below returns Date type for spelled month passed as parameter.

SELECT TO_DATE('2012/January/09', 'yyyy/Month/dd') "Oracle SQL TO_DATE"
FROM dual;

Oracle SQL TO_DATE query returns Date value for January 9, 2012.

Note: We have aliased TO_DATE(‘2012/January/09’, ‘yyyy/mm/dd’) as Oracle SQL TO_DATE.

---

Oracle TO_DATE Examples – Oracle TO_DATE Format

Oracle TO_DATE Format example below returns Date type for partially spelled month passed as parameter.

SELECT TO_DATE('2012/Jan/09', 'yyyy/Mon/dd') "Oracle TO_DATE Format"
FROM dual;

Oracle SQL TO_DATE Function returns Date value for January 9, 2012.

Note: In Oracle TO_DATE examples above we have aliased TO_DATE(‘2012/Jan/09’, ‘yyyy/mm/dd’) as Oracle TO_DATE Format.

---