Oracle FROM_TZ Function

Oracle FROM_TZ Function is used to convert a TIMESTAMP value to another TIMASTAMP with TIME ZONE value. Oracle FROM_TZ Function can be used with the SQL TRUNC Function and SQL TO_CHAR Function.

Oracle FROM_TZ Function Syntax

SELECT FROM_TZ(timestamp_value,timezone_value)
FROM table_name;

• ‘timestamp_value’ is the value that has to be converted to a TIMESTAMP with TIME ZONE value
• ‘timezone_value’ is the time zone that is used to convert the time stamp value to time stamp with time zone value .

Oracle FROM_TZ Function – Using SQL SELECT Statement Example

Oracle FROM_TZ Function is used with the SQL SELECT Statement.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value.

SELECT FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '5:30')
FROM DUAL;

Will return “12/15/2012 1:50:42.000000000 AM +05:30”

Oracle FROM_TZ Function – Using SQL TRUNC Function Example

Oracle FROM_TZ Function can be used with the SQL TRUNC Function.

For example, the SQL query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and returns only the date component.

SELECT TRUNC(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'))
FROM DUAL;

Will return “12/15/2012″.

Oracle FROM_TZ Function – Using SQL TO_CHAR Function Example

Oracle FROM_TZ Function can be used with the SQL TO_CHAR Function.

For example, the Oracle FROM_TZ Function query below converts TIMESTAMP value to another TIMASTAMP with TIME ZONE value and spells the Month.

SELECT TO_CHAR(FROM_TZ(TIMESTAMP '2012-12-15 01:50:42', '-7:00'),'DD-MONTH-RRRR')
FROM DUAL;

Will return “15-DECEMBER -2012″.