Oracle TO_DATE Function

Oracle TO_DATE FunctionOracle 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.


Tagged , , , . Bookmark the permalink.