Oracle TO_TIMESTAMP_TZ() Function

Oracle TO_TIMESTAMP_TZ() is a built-in function that converts the given string argument to a TIMESTAMP WITH TIME ZONE data type.

Oracle TO_TIMESTAMP_TZ() Syntax

Here is the syntax for the Oracle TO_TIMESTAMP_TZ() function:

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

Parameters

str

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

DEFAULT return_value ON CONVERSION ERROR

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

fmt

Optional. It indicates the format of the str parameter. If fmt is omitted, str must be in the default format of the TIMESTAMP WITH TIME ZONE data type.

'nlsparam'

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

Return Value

The Oracle TO_TIMESTAMP_TZ() function returns a value of the TIMESTAMP WITH TIME ZONE data type that is converted from the given string parameter.

If any parameter is NULL, TO_TIMESTAMP_TZ() returns NULL.

Oracle TO_TIMESTAMP_TZ() Examples

Here are several examples that demonstrate the usage of the Oracle TO_TIMESTAMP_TZ() function.

Basic Usage

The following converts the string '2023-02-14 10:11:12.123 +08:00' to a timestamp value with time zone:

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

SELECT
    TO_TIMESTAMP_TZ('2023-02-14 10:11:12.123 +08:00') Result
FROM dual;

Output:

RESULT
_______________________________________
2023-02-14 10:11:12.123000000 +08:00

The default format is determined by the NLS_TIMESTAMP_TZ_FORMAT parameter, and you can modify the default format of the TIMESTAMP WITH TIME ZONE data type using the following statement:

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

Format

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

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF TZR';
SELECT
    TO_TIMESTAMP_TZ(
        '2023-02-14 10:11:12.123 +08:00',
        'YYYY-MM-DD HH:MI:SS.FF TZH:TZM'
    ) Result
FROM dual;

Output:

RESULT
_______________________________________
2023-02-14 10:11:12.123000000 +08:00

Here is an example using a different format:

SELECT
    TO_TIMESTAMP_TZ(
        '14-Feb-2012 10:11:12.123 +08:00',
        'DD-Mon-RRRR HH24:MI:SS.FF TZH:TZM'
    ) Result
FROM dual;

Output:

RESULT
_______________________________________
2012-02-14 10:11:12.123000000 +08:00

Handling Errors

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

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

Output:

RESULT
_______________________________________
2023-02-14 10:11:12.123000000 +08:00

In this example, because '2025-02-14T10:11:12.123 +08:00' does not conform to the default format, a conversion error occurs, and TO_TIMESTAMP_TZ() returns the default value.

NULL Parameters

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

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

Output:

TO_TIMESTAMP_TZ(NULL)    TO_TIMESTAMP_TZ(NULL,NULL)
________________________ _____________________________
NULL                     NULL

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

Conclusion

The Oracle TO_TIMESTAMP_TZ() is a built-in function that converts a given string parameter to a value of type TIMESTAMP WITH TIME ZONE.