How To Use TO_DATE In Oracle

This tutorial is based on examples so it would be easier to understand. Oracle To_Date function converts built-in data types like string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to a DATE data type and this way you will be able to do with the value date operations. The date format is not required but then your date value has to be written in way of default database date format. Also the NLS_DATE_FORMAT setting is optional but it gives you an opportunity to export the date value in an another time region style. The syntax of Oracle To_Date is :

TO_DATE (<string>[,<date_format>] [,NLS_DATE_FORMAT]);

The first Oracle To_Date example has date November 20th 2010 written in a string as “2010/11/20“. Behind the date value we have declared the Oracle date format that describes the date value look. Oracle DBA recommends to use always the date format even it is optional. Using a date format your date value will be converted always correctly and the default database date format can be changed without affecting the existing code or SQL queries.

 SELECT TO_DATE ('2010/11/20','yyyy/mm/dd') AS my_date
   FROM dual;

The second example is written to use the same date value as above only with in lightly different format. The example above had from left to right first number of years (YYYY) then months (MM) and in the last days (DD). The following example has first number months (MM) then days (DD) and the last comes years (YYYY). Also the date format has changed among of the date value and reflects the date value structure.

 SELECT to_date ('11/20/2010','mm/dd/yyyy') AS my_date
   FROM dual;

We have been using “/“-characters to separate date numbers on the last two examples but using them is not required and the next example shows that the date value can contain only numbers. The value will be converted correctly while the date format is declared to reflect the value. The date has still remained to November 20th 2010.

 SELECT to_date ('20112010','ddmmyyyy') AS my_date
   FROM dual;

The Oracle To_Date function is quite flexible and you can leave away even some date values. The following example has only month and year numbers and once again the Oracle date format allows the To_Date function to do converting correctly. Since the day number is missing the date will become first day of month and the SQL query output has November 1st 2010.

 SELECT to_date ('11/2010','mm/yyyy') AS my_date
   FROM dual;

Also you can convert your string to date type using only a year number as on the following example. When you take a look at the last example that had no day number and the Oracle database returned the first day of a month but leaving away the month number you will receive the current month and not the first month of year as you may guessed. The example above has two columns where the first is converted date using year 2010 number and the second column has current date the April 15th 2011. Take a look at the converted column “MY_DATE” the date is April 1st 2010.

 SELECT to_date ('2010','yyyy') AS my_date, SYSDATE
   FROM dual;

The Oracle date format allows to import date values from different look to the database default format so always use the Oracle date format among with Oracle To_Date function. In addition, you can find a table below for Oracle date format elements to do conversions.

See Also:
Oracle Select Oracle Extract Oracle Trunc Oracle Date Format Oracle Home

Oracle Date and Time Format Elements

Element	TO_* datetime functions?	Description
AD A.D.	Yes	AD indicator with or without periods.
AM A.M.	Yes	Meridian indicator with or without periods.
BC B.C.	Yes	BC indicator with or without periods.
CC SCC		Century. If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year. If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year. For example, 2002 returns 21; 2000 returns 20.
D	Yes	Day of week (1-7). This element depends on the NLS territory of the session.
DAY	Yes	Name of day.
DD	Yes	Day of month (1-31).
DDD	Yes	Day of year (1-366).
DL	Yes	Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS	Yes	Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY	Yes	Abbreviated name of day.
E	Yes	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE	Yes	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]	Yes	Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM	Yes	Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX	Yes	Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH HH12	Yes	Hour of day (1-12).
HH24	Yes	Hour of day (0-23).
IW		Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I		Last 3, 2, or 1 digit(s) of ISO year.
IYYY		4-digit year based on the ISO standard.
J	Yes	Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI	Yes	Minute (0-59).
MM	Yes	Month (01-12; January = 01).
MON	Yes	Abbreviated name of month.
MONTH	Yes	Name of month.
PM P.M.	Yes	Meridian indicator with or without periods.
Q		Quarter of year (1, 2, 3, 4; January – March = 1).
RM	Yes	Roman numeral month (I-XII; January = I).
RR	Yes	Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR	Yes	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS	Yes	Second (0-59).
SSSSS	Yes	Seconds past midnight (0-86399).
TS	Yes	Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD	Yes	Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH	Yes	Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM	Yes	Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR	Yes	Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
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.
X	Yes	Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY	Yes	Year with comma in this position.
YEAR SYEAR		Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY SYYYY	Yes	4-digit year; S prefixes BC dates with a minus sign.
YYY YY Y	Yes	Last 3, 2, or 1 digit(s) of year.	YYYY SYYYY	Yes	4-digit year; S prefixes BC dates with a minus sign.