Conversions That Include Time Zone - Advanced SQL Engine - Teradata Database

SQL Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
tpf1598412463935.ditamap
dita:ditavalPath
tpf1598412463935.ditaval
dita:id
B035-1143
lifecycle
previous
Product Category
Teradata Vantageā„¢

The following rules apply to character-to-TIMESTAMP conversions that include time zone information:

  • If the target data type does not specify a time zone, for example, TIMESTAMP(0), the source character string may contain a time zone of the format +hh:mi or -hh:mi, but only if it appears immediately before or immediately after the time.

    For example, the following conversion is successful:

    SELECT CAST ( '2008-09-19 11:23:44-02:00'
    AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBHH:MI:SSBZ' );

    The following conversion is not successful because of the blank separator character between the time zone and the time:

    SELECT CAST ( '2008-01-19 +02:00 11:23:44'
    AS TIMESTAMP(0) FORMAT 'Y4-MM-DDBZBHH:MI:SS' );
  • If the source character string contains a time zone, and the target data type does not specify a time zone, the conversion uses the time zone in the character string to convert the character string to Universal Coordinated Time (UTC). This is done whether or not the FORMAT phrase contains the time zone formatting character.
    SELECT CAST ('2002-02-20 10:15:12+12:30' AS TIMESTAMP(0));
  • If the target FORMAT phrase includes time zone formatting characters, and the source character string does not contain a time zone, the output includes the session time zone. This is done whether or not the target data type specifies a time zone.
    SELECT CAST ('2002-02-20 10:15:12' 
    AS TIMESTAMP(0) WITH TIME ZONE FORMAT 'Y4-MM-DDBHH:MI:SSBZ');
  • If both the source character string and the target data type do not specify a time zone, the source character string is internally converted to UTC based on the current session time zone.