TIMESTAMP_TZ

The TIMESTAMP_TZ function returns a TIMESTAMP WITH TIME ZONE value from the input arguments.


>>-TIMESTAMP_TZ(expression-1-+---------------+-)---------------><
                             '-,expression-2-'

The schema is SYSIBM.

expression-1

An expression that returns a value of one of the following built-in data types:

a timestamp without time zone
a timestamp with time zone
a character string
a graphic string

If expression-1 is a character string or a graphic string, it must conform to the following rules:

It must not be a CLOB or DBCLOB
Its value must be a valid string representation of a timestamp without a time zone or a timestamp with a time zone value
It must have an actual length that is not greater than 255 bytes

For the valid formats of string representations of datetime values, see String representations of datetime values.

If expression-2 is specified, expression-1 must be a timestamp without a time zone, or a string representation of a timestamp without a time zone.

expression-2

An expression that returns a character string or a graphic string.

If expression-2 is a character string or graphic string, it must not be a CLOB or DBCLOB, and its value must be a valid string representation of a time zone in the format of '±th:tm' with values ranging from -12:59 to +14:00, where th represents time zone hour and tm represents time zone minute.

Start of change The result of the function is equivalent to invoking the CAST specification, as indicated in the following table: End of change

Table 1. TIMESTAMP_TZ function and equivalent CAST specification
TIMESTAMP_TZ function syntax	Equivalent CAST specification syntax
`TIMESTAMP_TZ(timestamp_wo_tz)`	`CAST(timestamp_wo_tz AS TIMESTAMP WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_wo_tz, n)`	`CAST(timestamp_wo_tz AS TIMESTAMP(n) WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_wo_tz, timezone)`	`CAST(CONCAT(VARCHAR(timestamp_wo_tz, timezone) AS TIMESTAMP WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_wo_tz, timezone, n)`	`CAST(CONCAT(VARCHAR(timestamp_wo_tz, timezone) AS TIMESTAMP(n) WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_w_tz)`	`CAST(timestamp_w_tz AS TIMESTAMP WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_w_tz, n)`	`CAST(timestamp_w_tz AS TIMESTAMP(n) WITH TIME ZONE)`
`TIMESTAMP_TZ(timestamp_w_tz, timezone)`	N/A
`TIMESTAMP_TZ(timestamp_w_tz, timezone, n)`	N/A
`timestamp_wo_tz` A timestamp without time zone value. `timestamp_w_tz` A timestamp with time zone value. `timezone` A time zone value. `n` The precision value.

When a string representation of a timestamp is a single-byte character set (SBCS) with a CCSID that is not the same as the default CCSID for SBCS data, that value is converted to the default CCSID for SBCS data before it is interpreted and converted to a timestamp value.

Syntax alternatives:

If only one argument is specified, the CAST specification should be used to ensure maximal portability. For more information, see CAST specification
FROM_TZ can be specified as a synonym for TIMESTAMP_TZ when TIMESTAMP_TZ specifies both expression-1 and expression-2.

Example 1: Assume that TIMES is a host variable with the value 2008-02-29-20.00.00.000000 and that TZ is a host variable with the value -3:00. Convert the value of TIMES and TZ to a timestamp with time zone.

SET :TIMESZ = TIMESTAMP_TZ(:TIMES, :TZ);

The host variable TIMESZ is set with the value that represents the timestamp with time zone as 2008-02-29-20.00.00.000000 -03:00. End of change