Character-to-TIME Conversions with Time Zone - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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

  • If the target data type does not specify a time zone (for example, TIME(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 ( '-02:0011:23:44'
AS TIME(0) );

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

    SELECT CAST ( '+02:00 11:23:44.56'
AS TIME(2) );
  • If the source character string contains a time zone, and the target data type does not specify a time zone, for example, TIME(0), the conversion uses the time zone in the character string to convert the character string to Universal Coordinated Time (UTC). This is done regardless of whether the FORMAT phrase contains the time zone formatting character.
    SELECT CAST ('10:15:12+12:30' 
AS TIME(0));
  • If the source character string does not contain a time zone, and the target data type specifies a time zone and a target FORMAT phrase that includes time zone formatting characters, the output includes the session time zone.
    SELECT CAST ('10:15:12' 
AS TIME(0) WITH TIME ZONE FORMAT 'HH: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.