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

Syntax

The syntax goes like this:

TO_TIMESTAMP(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 format of the TIMESTAMP data type, which is determined by the NLS_TIMESTAMP_FORMAT initialisation parameter (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_TIMESTAMP(
        '25-Aug-2030 18:10:35.123456789', 
        'DD-Mon-RRRR HH24:MI:SS.FF'
    )
FROM DUAL;

Result:

25/AUG/30 06:10:35.123456789 PM

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

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

Result:

DD/MON/RR HH12:MI:SSXFF AM

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_TIMESTAMP(
        '25-Aug-2030 18:10:35.123456789', 
        'DD-Mon-RRRR HH24:MI:SS.FF'
    )
FROM DUAL;

Result:

25-AUG-30 06.10.35.123456789 PM

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_TIMESTAMP(
        '25-AUG-30 06.10.35.123456789 PM'
    )
FROM DUAL;

Result:

25-AUG-30 06.10.35.123456789 PM

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

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

SELECT 
    TO_TIMESTAMP(
        '25-Aug-2030 18:10:35.123456789'
    )
FROM DUAL;

Result:

ORA-01849: hour must be between 1 and 12
01849. 00000 -  "hour must be between 1 and 12"
*Cause:    
*Action:

In this case, I passed a value that uses 24 hour time, but my NLS_TIMESTAMP_FORMAT parameter specifies a 12 hour clock with the AM/PM designator.

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

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 TIMESTAMP type.

Example:

SET NULL 'null';
SELECT 
    TO_TIMESTAMP(
    '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_TIMESTAMP(
        '25-agosto-2030 18:10:35.123456789', 
        'DD-Month-RRRR HH24:MI:SS.FF',
        'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

25-AUG-30 06.10.35.123456789 PM

Note that the result is still returned according to the current session’s NLS_TIMESTAMP_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_TIMESTAMP(
        '25-August-2030 18:10:35.123456789', 
        'DD-Month-RRRR HH24:MI:SS.FF',
        'NLS_DATE_LANGUAGE = SPANISH'
    )
FROM DUAL;

Result:

ORA-01843: not a valid month
01843. 00000 -  "not a valid month"
*Cause:    
*Action:

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_TIMESTAMP(
        '25-August-2030 18:10:35.123456789', 
        'DD-Month-RRRR HH24:MI:SS.FF',
        'NLS_DATE_LANGUAGE = English'
    )
FROM DUAL;

Result:

25-AUG-30 06.10.35.123456789 PM

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_TIMESTAMP(null)
FROM DUAL;

Result:

null

Invalid Argument Count

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

SELECT TO_TIMESTAMP()
FROM DUAL;

Result:

ORA-00938: not enough arguments for function
00938. 00000 -  "not enough arguments for function"
*Cause:    
*Action:
Error at Line: 8 Column: 8

However, passing too many arguments doesn’t seem to cause any issues, as long as the first three are valid:

SELECT 
    TO_TIMESTAMP(
        '25-Aug-2030 18:10:35.123456789', 
        'DD-Mon-RRRR HH24:MI:SS.FF',
        'NLS_DATE_LANGUAGE = English',
        'Oops!',
        'Dang!',
        'Homer',
        'Symptom'
    )
FROM DUAL;

Result:

25-AUG-30 06.10.35.123456789 PM