TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ is a SQL function that converts a STRING type to a TIMESTAMP WITH TIME ZONE data type. This conversion is based on the format model and the input string provided by the user. The format model explicitly states the format of the date time string for conversion.

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

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

Output

2021-04-01 12:00:00.0 -07:00

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.

For in-depth explanations and examples SQL keywords where you write your SQL, install our extension.