Skip to content
DBCode

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ( string1 VARCHAR2, [ format_mask VARCHAR2, ‘nlsparam VARCHAR2’] ) RETURN TIMESTAMP WITH TIME ZONE;

Section titled “TO_TIMESTAMP_TZ( string1 VARCHAR2, [ format_mask VARCHAR2, ‘nlsparam VARCHAR2’] ) RETURN TIMESTAMP WITH TIME ZONE;”
  • string1 varchar2: A string that represents a datetime, which will be converted into a TIMESTAMP WITH TIME ZONE format.
  • format_mask varchar2: Optional parameter. A string that determines how the function interprets the datetime information in string1. For example, ‘DD-MON-YYYY HH24
  • ‘nlsparam varchar2’: Optional parameter. This specifies the language in which month and day names and abbreviations are returned. The default value is determined by the NLS_DATE_LANGUAGE initialization parameter. A typical value for NLS_DATE_LANGUAGE is ‘AMERICAN’.

Example

Section titled “Example”
SELECT TO_TIMESTAMP_TZ('2021-04-01 12:00:00 -07:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')
FROM DUAL

Output

Section titled “Output”
2021-04-01 12:00:00.0 -07:00

Explanation

Section titled “Explanation”

The TO_TIMESTAMP_TZ function in Oracle takes a string and a format and converts the string into a TIMESTAMP WITH TIME ZONE datatype. In the provided example, a timestamp string '2021-04-01 12:00:00 -07:00' with the format 'YYYY-MM-DD HH:MI:SS TZH:TZM' is converted to a timestamp with time zone, resulting in 2021-04-01 12:00:00.0 -07:00.