Oracle TO_TIMESTAMP_TZ function

Last update on September 24 2024 07:34:17 (UTC/GMT +8 hours)

Description

TO_TIMESTAMP_TZ converts a value of char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.

The optional fmt specifies the format of char. If you omit fmt, then char must be in the default format of the TIMESTAMP WITH TIME ZONE datatype. The optional 'nlsparam' has the same purpose in this function as in the TO_CHAR function for date conversion.

Uses of Oracle TO_TIMESTAMP_TZ() Function

Time Zone Conversion: Convert string representations of date-time with time zone information into a standardized format.

Handling Global Data: Manage date-time data from different time zones efficiently.

Accurate Time Calculations: Perform operations and comparisons across time zones with precision.

Data Consistency: Standardize various date-time formats that include time zone details.

International Reports: Format time-sensitive data with time zone details for global reports and analytics.

Syntax:

TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])

Parameters:

Name	Description
char	The CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype value to be converted.
fmt (Optional)	Specifies the format of char. Default: Format of the TIMESTAMP WITH TIME ZONE.
nlsparam (Optional)	Specifies the language in which month and day names and abbreviations are returned. This argument can have this form : 'NLS_DATE_LANGUAGE = language'.

Pictorial Presentation

Examples: Oracle TO_TIMESTAMP_TZ function

The following statement converts a character string to a value of TIMESTAMP WITH TIME ZONE :

SQL> SELECT TO_TIMESTAMP_TZ('2015-02-01 12:00:00 -5:00',
    2     'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

Sample Output:

TO_TIMESTAMP_TZ('2015-02-0112:00:00-5:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
-----------------------------------------------------------------------
01-FEB-15 12.00.00.000000000 PM -05:00

Previous: TO_TIMESTAMP
Next: TO_DSINTERVAL