15.00 - DATE-to-Period Conversion - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)
Last Update
2018-09-24

DATE-to-Period Conversion

Casts as PERIOD(DATE) or PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]).

CAST Syntax

where:

 

Syntax element …

Specifies …

date_expression

a date expression to be converted.

period_data_type

the target Period type to which the date expression is to be converted.

period_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 data attribute phrases.

    Usage Notes

    A DATE value can be cast as PERIOD(DATE) or PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]) using the CAST function. If an attempt is made to cast a DATE value as PERIOD(TIME[(n)] [WITH TIME ZONE]), an error is reported.

    If the target type is PERIOD(DATE), the result beginning element is set to the source value. The result ending element is set to the result beginning bound plus one granule of the target type (that is, INTERVAL '1' DAY). If the result ending bound exceeds the maximum DATE value (that is, the source value is equal to the maximum DATE value), or the result ending bound equal to maximum DATE value (that is, the resulting ending bound value equal to value of UNTIL_CHANGED) an error is reported.

    If the target type is PERIOD(TIMESTAMP[(n)]), the result beginning element is set to the UTC value obtained using the current session time zone and a timestamp value formed from the source DATE value and a time portion of zero. The result ending element is set to the result beginning bound plus one granule of the target type (note that this cannot cause an error).

    If the target type is PERIOD(TIMESTAMP[(n)] WITH TIME ZONE), the time portion of the result beginning element is set to the UTC value obtained using the current session time zone and a timestamp value formed from the source DATE value and a time portion of zero. The time zone of the result beginning element is set to the current session time zone displacement. The result ending element is set to the result beginning bound plus one granule of the target type (note that this cannot cause an error).

    Note: The result has the same value for the beginning bound and last value.

    Example  

    In the following example, a DATE literal is cast as PERIOD(DATE). The result beginning bound is obtained from the source. The result ending element is set to the result beginning bound plus INTERVAL '1' DAY.

       SELECT CAST(DATE '2005-02-03' AS PERIOD(DATE));

    The following PERIOD(DATE) value is returned:

       ('2005-02-03', '2005-02-04')

    Example  

    In the following example, a DATE literal is cast as PERIOD(TIMESTAMP(4)). The result beginning bound is formed from the DATE literal and a time portion of zero. The result ending element is set to the result beginning bound plus INTERVAL '0.0001' SECOND.

       SELECT CAST(DATE '2005-02-03' AS PERIOD(TIMESTAMP(4)));

    The following PERIOD(TIMESTAMP(4)) value is returned:

       ('2005-02-03 00:00:00.0000', '2005-02-03 00:00:00.0001')

    Related Topics

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