TO_DATE() Function in Oracle

In Oracle Database, the TO_DATE() function converts its argument to a value of DATE data type.

Syntax

The syntax goes like this:

TO_DATE(char [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

The char argument can be any expression that evaluates to a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type.

The optional fmt specifies the format of char. If you omit fmt, then char must be in the default date format. The default date format is determined by the NLS_DATE_FORMAT initialisation parameter, which itself is implicitly set by the NLS_TERRITORY parameter, but can also be set explicitly (see How to Check the Date Format of your Oracle Session).

The optional 'nlsparam' argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:

'NLS_DATE_LANGUAGE = language'

Example

Here’s a basic example to demonstrate:

SELECT 
    TO_DATE(
        '25-Aug-2030', 
        'DD-Mon-RRRR'
    )
FROM DUAL;

Result:

25/AUG/30

The format of the result is determined by your session’s NLS_DATE_FORMAT parameter. We can check the value of the NLS_DATE_FORMAT parameter by querying the V$NLS_PARAMETERS view:

SELECT VALUE
FROM V$NLS_PARAMETERS
WHERE PARAMETER = 'NLS_DATE_FORMAT';

Result:

DD/MON/RR

The default value of the NLS_TIMESTAMP_FORMAT parameter is derived from the NLS_TERRITORY parameter. In my case the NLS_TERRITORY parameter is AUSTRALIA.

Here’s what happens when I change the NLS_TERRITORY parameter to another territory, then call TO_TIMESTAMP() again:

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
SELECT 
    TO_DATE(
        '25-Aug-2030', 
        'DD-Mon-RRRR'
    )
FROM DUAL;

Result:

25-AUG-30

This time the result is returned in a different format.

You can alternatively change the value of the NLS_TIMESTAMP_FORMAT parameter directly. This will change that parameter without affecting other parameters. See How to Change the Date Format in your Oracle Session for more information and examples.

Default Format

In this example I omit the fmt argument:

SELECT 
    TO_DATE(
        '25-AUG-30'
    )
FROM DUAL;

Result:

25-AUG-30

When doing this, the argument must be in the default format of the DATE data type, which is determined by the NLS_DATE_FORMAT initialisation parameter.

Here’s an example of what happens when we pass a value that doesn’t conform to this format:

SELECT 
    TO_DATE(
        '2030-08-25'
    )
FROM DUAL;

Result:

Error report -
ORA-01861: literal does not match format string

To fix this, I would either need to change my input, or change the value of the NLS_DATE_FORMAT parameter.

It’s generally not good practice to omit the format model. In other words, it’s good practice to always provide a format model when using this function. Here’s a full list of datetime format elements that can be used when constructing a format model.

Provide a Default Value on Conversion Error

You also have the option of providing a value to return in the event that there’s an error while converting the argument to a DATE type.

Example:

SET NULL 'null';
SELECT 
    TO_DATE(
    'Oops!'
    DEFAULT null ON CONVERSION ERROR
    )
FROM DUAL;

Result:

null

In this case I specified that null should be returned whenever there’s a conversion error.

By default, SQLcl and SQL*Plus return a blank space whenever null occurs as a result of a SQL SELECT statement.

However, you can use SET NULL to specify a different string to be returned. Here I specified that the string null should be returned.

The 'nlsparam' Argument

The optional 'nlsparam' argument specifies the language in which month and day names and abbreviations are provided. It takes the following form:

'NLS_DATE_LANGUAGE = language'

Example

SELECT 
    TO_DATE(
        '25-agosto-2030', 
        'DD-Month-RRRR',
        'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

25-AUG-30

Note that the result is still returned according to the current session’s NLS_DATE_FORMAT parameter. It’s just that the input was in a different language.

Here’s what happens when I change agosto to August while using the same 'nlsparam' value:

SELECT 
    TO_DATE(
        '25-August-2030', 
        'DD-Month-RRRR',
        'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

Error report -
ORA-01843: not a valid month

This happened because I didn’t provide the date in the language specified by the 'nlsparam' argument (Spanish).

Changing it to English solves this problem:

SELECT 
    TO_DATE(
        '25-August-2030', 
        'DD-Month-RRRR',
        'NLS_DATE_LANGUAGE = English'
    )
FROM DUAL;

Result:

25-AUG-30

See How to Return a List of Supported Languages in Oracle if it helps.

Null Arguments

Passing null results in null:

SET NULL 'null';
SELECT 
    TO_DATE(null)
FROM DUAL;

Result:

null

Invalid Argument Count

Calling the function without passing any arguments results in an error:

SELECT TO_DATE()
FROM DUAL;

Result:

SQL Error: ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"

And passing too many arguments also results in an error:

SELECT 
    TO_DATE(
        '25-Aug-2030', 
        'DD-Mon-RRRR',
        'NLS_DATE_LANGUAGE = English',
        'Gosh',
        'Dang'
    )
FROM DUAL;

Result:

SQL Error: ORA-00939: too many arguments for function
00939. 00000 -  "too many arguments for function"