Oracle FROM_TZ() Function

Oracle FROM_TZ() is a built-in function that converts a timestamp value and a time zone value into a timestamp value with time zone.

Oracle FROM_TZ() Syntax

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

FROM_TZ(timestamp_value, time_zone_value)

Parameters

timestamp_value

Required.

time_zone_value

Required. It is a string in the format of 'TZH:TZM' or an expression that returns a string in the TZR format with TZD.

Return Value

The Oracle FROM_TZ() function returns a timestamp value with time zone.

Oracle FROM_TZ() Examples

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

Basic Usage

ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SSXFF TZR';
SELECT
    FROM_TZ(TIMESTAMP '2023-02-11 12:13:14', '+09:00') Result
FROM dual;

Output:

RESULT
_______________________________________
2023-02-11 12:13:14.000000000 +09:00

Here, we use the ALTER SESSION statement to modify the date display format of the current session.

NULL Parameters

You cannot provide a NULL value for the first parameter, or Oracle will report an error.

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

Output:

SQL Error: ORA-00932: inconsistent datatypes: expected TIMESTAMP got CHAR

If you provide a NULL value for the second parameter, FROM_TZ() will return NULL.

SET NULL 'NULL';
SELECT
    FROM_TZ(TIMESTAMP '2023-02-11 12:13:14', NULL)
FROM dual;

Output:

FROM_TZ(TIMESTAMP'2023-02-1112:13:14',NULL)
______________________________________________
NULL

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

Conclusion

The Oracle FROM_TZ() is a built-in function that converts a timestamp value and a time zone value into a timestamp value with time zone.