DATE-to-TIMESTAMP Conversion - Teradata Database

SQL Functions, Operators, Expressions, and Predicates
Product
Teradata Database
Release Number
15.00
Language
English (United States)
Last Update
2018-09-24
dita:id
B035-1145
lifecycle
previous
Product Category
Teradata® Database

DATE-to-TIMESTAMP Conversion

Purpose

Converts a DATE value to a TIMESTAMP or TIMESTAMP WITH TIME ZONE value.

CAST Syntax

where:

 

Syntax element …

Specifies …

date_expression

a date expression to be converted.

fractional_seconds_precision

a single digit representing the number of significant digits in the fractional portion of the SECOND field.

Values for fractional_seconds_precision range from 0 through 6 inclusive.

The default precision is 6.

AT LOCAL

that the time zone displacement based on the current session time zone is used.

This is the default.

AT [TIME ZONE] expression

that the time zone displacement defined by expression is used. The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

AT [TIME ZONE] time_zone_string

that time_zone_string is used to determine the time zone displacement used for the CAST. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

timestamp_data_attribute

one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
    		•

    ANSI Compliance

    This is ANSI SQL:2011 compliant.

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

    The AT clause is ANSI SQL:2011 compliant.

    Note: As an extension to ANSI, the AT clause is supported when converting from DATE to TIMESTAMP using CAST. In addition, you can specify the time zone displacement using additional expressions besides an INTERVAL expression.TIMESTAMP (without time zone) is not ANSI SQL:2011 compliant. Teradata Database internally converts a TIMESTAMP value to UTC based on the current session time zone or on a specified time zone.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    date_expression

    a date expression to be converted.

    data_attribute

    one of the following optional data attributes:

  • FORMAT
  • NAMED
  • TITLE
    		•

    fractional_seconds_precision

    a single digit representing the number of significant digits in the fractional portion of the SECOND field.

    Values for fractional_seconds_precision range from 0 through 6 inclusive.

    The default precision is 6.

    AT LOCAL

    that the time zone displacement based on the current session time zone is used.

    This is the default.

    AT [TIME ZONE] expression

    that the time zone displacement defined by expression is used. The data type of expression should be INTERVAL HOUR(2) TO MINUTE or it must be a data type that can be implicitly converted to INTERVAL HOUR(2) TO MINUTE. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

    AT [TIME ZONE] time_zone_string

    that time_zone_string is used to determine the time zone displacement used for the conversion. For details, see “AT LOCAL and AT TIME ZONE Time Zone Specifiers” on page 827.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    The AT clause is ANSI SQL:2011 compliant.

    Note: As an extension to ANSI, the AT clause is supported when converting from DATE to TIMESTAMP using Teradata Conversion Syntax. In addition, you can specify the time zone displacement using additional expressions besides an INTERVAL expression.TIMESTAMP (without time zone) is not ANSI SQL:2011 compliant. Teradata Database internally converts a TIMESTAMP value to UTC based on the current session time zone or on a specified time zone.

    Usage Notes

    The following table shows the result of the CAST function or Teradata conversion based on the various options specified. If the target precision is higher than zero, trailing zeros are added in the result to adjust the precision.

     

    IF you specify...

    THEN...

    AT LOCAL

    a local timestamp value is formed from the source date_expression with the time portion set to '00:00:00'. Then, the result is formed from this local timestamp value adjusted to UTC by subtracting the time zone displacement based on the current session time zone.

    This is the same as not specifying the AT clause.

    AT expression
    or
    AT TIME ZONE expression

    a local timestamp value is formed from the source date_expression with the time portion set to '00:00:00'. Then, the result is formed from this local timestamp value adjusted to UTC by subtracting the time zone displacement defined by expression.

    AT time_zone_string
    or
    AT TIME ZONE time_zone_string

    a local timestamp value is formed from the source date_expression with the time portion set to '00:00:00'. The time zone displacement is determined based on time_zone_string and the local timestamp value. Then, the result is formed from the local timestamp value adjusted to UTC by subtracting the time zone displacement.

    Implicit DATE-to-TIMESTAMP Conversion

    Teradata Database performs implicit conversion from DATE to TIMESTAMP types in some cases. See “Implicit Conversion of DateTime types” on page 586.

    The following conversions are supported:

     

    From source type...

    To target type...

    DATE1

    TIMESTAMP

    TIMESTAMP WITH TIME ZONE
    1
    ANSIDate dateform mode or IntegerDate dateform mode

    The TIMESTAMP value is always converted to DATE in case of comparison. See “TIMESTAMP-to-DATE Conversion” on page 731.

    Example  

    In this example, the result of the CAST is the timestamp formed from the source expression value '2008-05-14' and the default time '00:00:00' adjusted to UTC by the current session time zone displacement, INTERVAL '01:00' HOUR TO MINUTE. Thus, the value of the CAST is '2008-05-13 23:00:00' at UTC.

    The result value of the CAST at UTC is adjusted to the current session time zone displacement, INTERVAL '01:00' HOUR TO MINUTE, so the result of the SELECT statements is: TIMESTAMP '2008-05-14 00:00:00'.

       SET TIME ZONE INTERVAL '01:00' HOUR TO MINUTE;
     
       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0));
       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0) AT LOCAL);

    Example  

    In this example, the result of the CAST is the timestamp formed from the source expression value '2008-05-14' and the default time '00:00:00' adjusted to UTC by the current session time zone displacement, INTERVAL '06:00' HOUR TO MINUTE. Thus, the value of the CAST is '2008-05-13 18:00:00' at UTC with the current session time zone displacement INTERVAL '06:00' HOUR TO MINUTE.

    The result value of the CAST at UTC is adjusted to its time zone displacement, INTERVAL '06:00' HOUR TO MINUTE, so the result of the SELECT statements is: TIMESTAMP '2008-05-14 00:00:00+06:00'.

       SET TIME ZONE INTERVAL '06:00' HOUR TO MINUTE;
     
       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0) WITH TIME ZONE);
       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0) WITH TIME ZONE
          AT LOCAL);

    Example  

    In the following SELECT statement, the result of the CAST is the timestamp formed from the date '2008-05-14' and the default time '00:00:00' adjusted to UTC by the specified time zone displacement, INTERVAL -'08:00' HOUR TO MINUTE. Thus, the value of the CAST is '2008-05-14 08:00:00' at UTC.

    The result value of the CAST at UTC is adjusted to the current session time zone displacement, INTERVAL '05:00' HOUR TO MINUTE, so the result of the SELECT statement is: TIMESTAMP '2008-05-14 13:00:00'.

       SET TIME ZONE INTERVAL '05:00' HOUR TO MINUTE;
     
       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0) AT -8);

    Consider the following SELECT statement:

       SELECT CAST(DATE '2008-05-14' AS TIMESTAMP(0) WITH TIME ZONE AT -8);

    In this case, the result of the CAST is the timestamp formed from the source expression value '2008-05-14' and the default time '00:00:00' adjusted to UTC by the specified time zone displacement, INTERVAL -'08:00' HOUR TO MINUTE. Thus, the value of the CAST is '2008-05-14 08:00:00' at UTC with the specified time zone displacement INTERVAL -'08:00' HOUR TO MINUTE.

    The result value of the CAST at UTC is adjusted to its time zone displacement, INTERVAL ‑'08:00' HOUR TO MINUTE, so the result of the SELECT statement is: TIMESTAMP '2008-05-14 00:00:00-08:00'. The current session time zone has no effect.

    Example  

    In this example, the current timestamp is:

                   Current TimeStamp(6)
       --------------------------------
       2010-03-09 19:23:27.620000+00:00

    The following statement converts the DATE value '2010-03-09' to a TIMESTAMP value, where the time zone displacement is based on the time zone string, 'America Pacific'.

       SELECT CAST(DATE '2010-03-09' AS TIMESTAMP(0) AT 'America Pacific');

    The result of the query is:

                2010-03-09
       -------------------
       2010-03-09 08:00:00

    Related Topics

    For details on data types and data attributes, see SQL Data Types and Literals.