ORACLE TO_DATE - Online Tech Support
  Home
 

This tutorial page is based on examples to be easier to follow. You can find Oracle Date Format types on this link. The TO_DATE syntax is following:

TO_DATE('date_value',['date_mask'],['NLS_DATE_FORMAT']);

The first example to use date format in Oracle would be as the following example:

SELECT TO_DATE('20120921','YYYYMMDD')
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

On the example above the Oracle date format has set a date mask after string ‘20120921’. The date mask is a text that is written or formatted in the same order as is the date value. For example the example has year as “YYYY” + month as “MM” + day number as “DD“. And the Oracle date value is September 21st 2012.

To find out the last date of some year you could do it as the following example. In that example we are looking for the last day number for year “2000“.

SELECT TO_DATE('20010101','YYYYMMDD') -1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

The Oracle SQL has date the January 1st 2001 and we are subtracting one day off from it. In addition, you can use the same way to find out the last day of the February month. The reason to do it is because the February month has a different amount of days in every forth year. The example below with Oracle date format will return the last day of February 1998 and it is “28-FEB-98“.

SELECT TO_DATE('19980301','YYYYMMDD') -1
  FROM DUAL;

online tech support computer help computer technician computer problems computer oracle date format date format oracle to_date oracle to_date in oracle sql date format oracle sql date format in oracle sql to_date oracle sql to_date in oracle sql retirement planning retirement

To achieve the last day of February 1998 we took the next month’s first day (March 1st 1998) and subtracted 1 day.

For additional oracle date format types please see the first link above and find out more date format oracle types.


See Also:
Oracle Date Difference Oracle TO_DATE Oracle Interval

 

This online tutorial page is based on examples to be easier to follow. The Oracle To_Date function converts data types like string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to a DATE data type and this way you will be able to do date operations. You can leave the date format away if your date value in string has written in default database date format. Also NLS_DATE_FORMAT is optional but it gives you an opportunity to export the date value in another time region look. The Oracle To_Date syntax is following:

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

The first example has date November 20th 2010 in string as “2010/11/20” and behind it we have declared the Oracle date format that describes the date value look. Online Tech Support 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 any code.

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

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

The second example with Oracle To_Date is using the same date value as above only with different format. The example above had number of years then months and in the last days but the following example has first number months then days and to last comes years. Also take a look at the date format it has changed among of the date value and reflects the date structure.

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

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

We have been using “/“-characters to separate date numbers on the last two examples above but using them is not obligated 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. Take a look at the output below the date has still remained to November 20th 2010.

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

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

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

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

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format

To convert a string to date type using only a year number is described on the following example above. The last example had no day number and the Oracle database took the first number of declared 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. Now have 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;

online tech support computer help computer technician computer problems computer oracle to_date oracle to_date in oracle sql to_date oracle sql to_date in oracle sql database oracle database sql oracle date format oracle date format in oracle sql date format



See Also:
Oracle Select Oracle Date Format Oracle Date Difference 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.