CAST TIME-to-TIMESTAMP Conversion | VantageCloud Lake - TIME-to-TIMESTAMP Conversion with CAST - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

ANSI Compliance

This statement is ANSI SQL:2011 compliant.

As an extension to ANSI, CAST permits the use of the FORMAT phrase to enable alternative output formatting of DateTime data.

The AT clause is ANSI SQL:2011 compliant.

As an extension to ANSI, the AT clause is supported when using CAST to convert from TIME to TIMESTAMP. In addition, you can specify the time zone displacement using additional expressions besides an INTERVAL expression.

TIME (without time zone) and TIMESTAMP (without time zone) are not ANSI SQL:2011 compliant. Vantage internally converts a TIME or TIMESTAMP value to UTC based on the current session time zone or on a specified time zone.

Syntax

CAST (
  time_expression AS TIMESTAMP
  
  [ ( fractional_seconds_precision ) ]
  
  [ WITH TIME ZONE ]
  
  [ AT { LOCAL | SOURCE [ TIME ZONE ] |
         [ TIME ZONE ] { expression | time_zone_string }
       }
  ]
  
  [ data_attribute [...] ]
)

Syntax Elements

time_expression

The TIME expression to be converted.

fractional_seconds_precision

A single digit representing the number of significant digits in the fractional portion of the SECOND field. The valid range is 0 through 6. The default is 6.

AT LOCAL

Use the time zone displacement based on the current session time zone.

AT SOURCE [TIME ZONE]
Use the time zone associated with timestamp_expression when:
  • AT SOURCE TIME ZONE is specified.
  • AT SOURCE is specified without TIME ZONE and there is no column named source in the scope.

Otherwise, if AT SOURCE is specified without TIME ZONE and a column named source exists, then SOURCE references this column, and the value of the column is used as the time zone displacement for the CAST. If needed, the column value is implicitly converted to type INTERVAL HOUR(2) TO MINUTE.

AT [TIME ZONE] expression
Use the time zone displacement defined by expression.
The data type of expression must be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE.
AT [TIME ZONE] time_zone_string
time_zone_string determines the time zone displacement.
data_attribute
One of the following data attributes:
  • FORMAT
  • NAMED
  • TITLE