Character-to-TIMESTAMP Conversions with Time Zone - 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 the time zone 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.