Oracle TO_DATE Function – SQL Syntax Examples

AshokTechnical Tips2 Comments

The Oracle TO_DATE SQL function is used to convert a TEXT or NTEXT representation of a date into an Oracle DATETIME value.

Below shows the TO_DATE function along with the arguments it takes:

1
to_date(string)
1
to_date(string, date_format)
1
to_date(string, date_format, options)

The Oracle TO_DATE function returns a DATETIME representation of the string input using the date_format input.

Following are important rules to follow along with syntax exemplifying the implications of the rules.

  1. By default, strings following the formats of DD-MON-YYYY, DD-MON-YY, DD-MONTH-YYYY, DD-MONTH-YY can automatically be converted without the need for a date_format.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    
    SELECT to_date('01-JAN-2009') FROM dual
    --returns: 01-JAN-09
     
    SELECT to_date('01-JAN-09') FROM dual
    --returns: 01-JAN-09
     
    SELECT to_date('01-JANUARY-2009') FROM dual
    --returns: 01-JAN-09
     
    SELECT to_date('01-01-09') FROM dual
    ORA-01843: NOT a valid MONTH
     
    SELECT to_date('1-JANUARY-2009') FROM dual
    --returns: 01-JAN-09
     
    SELECT to_date('0-JAN-2009') FROM dual
    --returns: ORA-01847: day of month must be between 1 and last day of month
  2. You want to ensure that your input string both matches the date_format and is also a valid date.
    1
    2
    3
    4
    5
    
    SELECT to_date('Thursday 01/01/2009','DAY MM/DD/YYYY') FROM dual
    --returns: 01-JAN-09
     
    SELECT to_date('Monday 01/01/2009','DAY MM/DD/YYYY') FROM dual
    --returns: ORA-01835: day of week conflicts with Julian date

Sortable Table of Date Format Mask Options

Following is a sortable table of the most common date_formats that can be used when defining your date_format mask (to sort, click on a column heading):

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

2 Comments on “Oracle TO_DATE Function – SQL Syntax Examples”

  1. I really like your style, the particular fact that your site is a tad bit different tends to make it so helpful, I get completely fed up of viewing the same stuff just about all almost daily. I have I simply just happened on this page by you Many thanks.

Leave a Reply to manoj Cancel reply

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