Oracle TO_TIMESTAMP() Function

Oracle TO_TIMESTAMP() is a built-in function that converts a given string parameter to a value of type TIMESTAMP.

Syntax of Oracle TO_TIMESTAMP()

Here is the syntax of the Oracle TO_TIMESTAMP() function:

TO_TIMESTAMP(str [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

Parameters

str

Required. The string to be converted. It can be any character string expression that evaluates to the CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data types.

DEFAULT return_value ON CONVERSION ERROR

Optional. It allows you to specify a value to return when a conversion error occurs. Note that the data type of return_value is the same as that of the str parameter and is given in the correct format.

fmt

Optional. Indicates the format of the str parameter. If fmt is omitted, str must use the default format for the TIMESTAMP data type.

'nlsparam'

Optional. You can use this parameter to set the language with the form 'NLS_DATE_LANGUAGE = language', where language is the language name.

Return Value

The Oracle TO_TIMESTAMP() function returns a value of type TIMESTAMP that is derived from the given string parameter.

If any of the parameters is NULL, TO_TIMESTAMP() will return NULL.

Examples of Oracle TO_TIMESTAMP()

Here are several examples that demonstrate how to use the Oracle TO_TIMESTAMP() function.

Basic Usage

The following converts the string '2023-02-14 10:11:12.123' to a timestamp value:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';

SELECT
    TO_TIMESTAMP('2023-02-14 10:11:12.123') Result
FROM dual;

Output:

RESULT
________________________________
2023-02-14 10:11:12.123000000

The default format is determined by the NLS_TIMESTAMP_FORMAT parameter, which you can modify with the following statement.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';

Format

The Oracle TO_TIMESTAMP() function allows you to specify a format for a string. This statement is equivalent to the previous one:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
SELECT
    TO_TIMESTAMP(
        '2023-02-14 10:11:12.123',
        'YYYY-MM-DD HH:MI:SS.FF'
    ) Result
FROM dual;

Output:

RESULT
________________________________
2023-02-14 10:11:12.123000000

Here is an example that uses a different format:

SELECT
    TO_TIMESTAMP(
        '14-Feb-2012 10:11:12.123',
        'DD-Mon-RRRR HH24:MI:SS.FF'
    ) Result
FROM dual;

Output:

RESULT
________________________________
2012-02-14 10:11:12.123000000

Handling Errors

The Oracle TO_TIMESTAMP() function allows you to specify a default value to use when a conversion error occurs.

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF';
SELECT
    TO_TIMESTAMP(
        '2025-02-14T10:11:12.123'
        DEFAULT '2023-02-14 10:11:12.123' ON CONVERSION ERROR
    ) Result
FROM dual;

Output:

RESULT
________________________________
2023-02-14 10:11:12.123000000

In this example, a conversion error occurred because '2025-02-14T10:11:12.123' did not match the default format, and TO_TIMESTAMP() returned the default value.

NULL Parameters

If any parameter is NULL, TO_TIMESTAMP() will return NULL.

SET NULL 'NULL';
SELECT
    TO_TIMESTAMP(NULL) ,
    TO_TIMESTAMP(NULL, NULL)
FROM dual;

Output:

TO_TIMESTAMP(NULL)    TO_TIMESTAMP(NULL,NULL)
________________________ _____________________________
NULL                     NULL

In this example, we use the statement SET NULL 'NULL'; to display NULL values as the string 'NULL'.

Conclusion

Oracle’s TO_TIMESTAMP() is a built-in function that converts a given string parameter to a value of type TIMESTAMP.