The Oracle TO_DATE function is one of the most common and useful string manipulation functions in Oracle, but it can be confusing.
I’ll explain how to use the TO_DATE function in this article.
Purpose of the Oracle TO_DATE Function
The purpose of the TO_DATE function in Oracle is to convert a character value to a date value.
In reality, it converts any value which has a data type of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 into a value which has the data type of DATE.
It doesn’t convert the value into any of the other datetime datatypes. To use TO_DATE with time in the destination value, you’ll need to use either TO_TIMESTAMP, TO_TIMESTAMP_TZ (timezone functions), TO_DSINTERVAL, or TO_YMINTERVAL (interval functions).
The syntax of the Oracle TO_DATE function is:
TO_DATE( charvalue, [format_mask], [nls_date_language] )
The parameters of the Oracle TO_DATE function are:
- charvalue (mandatory): This is the character value, or string, to convert into a data type. This is the main input to the function and should be one of the string data types mentioned earlier.
- format_mask (optional): This is the format of the input value (charvalue). This is used to determine how the charvalue is made up, especially for data formats which are all numbers.
- nls_date_language (optional): This value is used to determine how the output value is displayed.
Oracle TO_DATE Format Mask
The Oracle TO_DATE function includes a “format mask”. The format mask is helpful as it allows you to specify what format the date value was provided in.
If, for example you have a charvalue of ’02/05/2017′, does this mean May 2nd or Feb 5th? The result of the Oracle TO_DATE function would depend on the format you specify, and may be obvious depending on what region you’re from.
To make it clear, we need to specify some values for the format mask as part of the Oracle TO_DATE function.
The possible values are:
|YEAR||Year, spelled out in full words|
|YYY||Last 3 digits of year|
|YY||Last 2 digits of year|
|Y||Last digit of year|
|IYY||Last 3 digits of ISO year|
|IY||Last 2 digits of ISO year|
|I||Last digit of ISO year|
|IYYY||4-digit year, which is based on the ISO standard|
|RRRR||This format accepts a 2-digit year, and returns a 4-digit year. If the provided value is between 0 and 49, it will return a year greater than or equal to 2000. If the provided value is between 50 and 99, it will return a year less than 2000|
|Q||Quarter of year, from 1 to 4. JAN to MAR = 1|
|MM||Month, from 01 to 12. JAN = 01|
|MON||Abbreviated name of month.|
|MONTH||Name of month, padded with blanks to length of 9 characters.|
|RM||Roman numeral month, from I to XII. JAN = I.|
|WW||Week of year, from 1 to 53. Week 1 starts on the first day of the year, and continues to the seventh day of the year.|
|W||Week of month, from 1 to 5. Week 1 starts on the first day of the month and ends on the seventh.|
|IW||Week of year, from 1 to 52 or 1 to 53, based on the ISO standard.|
|D||Day of week, from 1 to 7.|
|DAY||Name of day.|
|DD||Day of month, from 1 to 31.|
|DDD||Day of year, from 1 to 366.|
|DY||Abbreviated name of day.|
|J||Julian day, which is the number of days since January 1, 4712 BC.|
|HH||Hour of day, from 1 to 12.|
|HH12||Hour of day, from 1 to 12.|
|HH24||Hour of day, from 0 to 23.|
|MI||Minute, from 0 to 59|
|SS||Second, from 0 to 59|
|SSSSS||Seconds past midnight, from 0 to 86399.|
|FF||Fractional seconds. This uses a value from 1 to 9 after FF, to indicate the number of digits in the fractional seconds (e.g. FF7)|
|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|
|TZH||Time zone hour.|
|TZM||Time zone minute.|
|TZR||Time zone region.|
As you can see, TO_DATE with time and date is possible, and it depends on the value being supplied.
Oracle NLS_DATE_LANGUAGE Explanation
The NLS_DATE_LANGUAGE can be specified in the Oracle TO_DATE function to determine how the date is shown. As you may know, different regions show the same date in different ways:
- China: yyyy-mm-dd
- UK: dd/mm/yyyy
- US: mm/dd/yyyy
To use the NLS_DATE_LANGUAGE as part of this function, you can use any language listed under Table A-1 on the Oracle Language page. Some examples are American, English, French, German, and Swedish.
Examples of the TO_DATE Function
Here are some examples of the TO_DATE function. I find that examples are the best way for me to learn about code, even with the explanation above.
Example 1 – Simple Query
This query is just a straightforward TO_DATE query with no formats specified. It assumes the default format for your Oracle installation.
SELECT TO_DATE('16-OCT-2022') AS date_test FROM DUAL;
Example 2 – With Format
This query shows the same date, but with the format specified.
SELECT TO_DATE('16-10-2022', 'DD-MM-YYYY') AS date_test FROM DUAL;
Example 3 – Different Format
This query shows a date with a different format specified, in YYYY/MM/DD format.
SELECT TO_DATE('2022/10/14', 'YYYY/MM/DD') AS date_test FROM DUAL;
Example 4 – Format with No Separators
This query shows a date with no separators for the values.
SELECT TO_DATE('20221012', 'YYYYMMDD') AS date_test FROM DUAL;
Example 5 – Different Date
This query is a different date using a specific format.
SELECT TO_DATE('08-10-2022', 'DD-MM-YYYY') AS date_test FROM DUAL;
Some functions which are similar to the Oracle TO_DATE function are:
- TO_CHAR – converts a value to a VARCHAR2 type
- TO_NUMBER – converts a value to a NUMBER type
- CAST – used for many types, including dates
You can find a full list of Oracle functions here.
Image courtesy of digitalart / FreeDigitalPhotos.net