Oracle TO_DATE Function Usage, Tips & Examples

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).

 

Syntax

The syntax of the Oracle TO_DATE function is:

TO_DATE( charvalue, [format_mask], [nls_date_language] )

 

Parameters

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 that are all numbers.
• nls_date_language (optional): This value is used to determine how the output value is displayed.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

 

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

Parameter	Explanation
YEAR	Year, spelled out in full words
YYYY	4-digit year
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

 

Month

Parameter	Explanation
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.

 

Week

Parameter	Explanation
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.

 

Day

Parameter	Explanation
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.

 

Time

Parameter	Explanation
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)

 

Indicators

Parameter	Explanation
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;

Result:

DATE_TEST

16/OCT/22

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;

Result:

DATE_TEST

16/OCT/22

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;

Result:

DATE_TEST

14/OCT/22

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;

Result:

DATE_TEST

12/OCT/22

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;

Result:

DATE_TEST

08/OCT/22

 

Similar Functions

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.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here: