15.00 - Period-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)

Period-to-Period Conversion

CAST Syntax

where:

 

Syntax element …

Specifies …

period_expression

the Period data expression to be converted.

period_data_type

the optional Period type to which period_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.

    Compatible Types

    The following table describes the allowed combinations of source and target types when both the source and the target types are Period data types.

     

    Source Type

    Target Type

    PERIOD(DATE)

    PERIOD(DATE)

    PERIOD(TIMESTAMP[(m)] [WITH TIME ZONE])

    PERIOD(TIME[(n)] [WITH TIME ZONE])

    PERIOD(TIME[(m)] [WITH TIME ZONE])

    where m is the target precision, m must be greater than or equal to the source precision n. The default for m is 6.

    PERIOD(TIMESTAMP[(m)] [WITH TIME ZONE])

    where m is the target precision, m must be greater than or equal to the source precision n. The default for m is 6.

    PERIOD(TIMESTAMP[(n)] WITH TIME ZONE)

    PERIOD(DATE)

    PERIOD(TIME[(m)] [WITH TIME ZONE])

    where m is the target precision, m must be greater than or equal to the source precision n. The default for m is 6.

    PERIOD(TIMESTAMP[(m)] [WITH TIME ZONE])

    where m is the target precision, m must be greater than or equal to the source precision n. The default for m is 6.

    PERIOD(DATE) to PERIOD(TIMESTAMP)

    A PERIOD(DATE) value can be cast as PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]) using the CAST function.

    The UTC value of the result elements are obtained after adjustment with respect to the current session time zone from the timestamps created by setting the date portion to the corresponding source elements and the time portions to 0. If the target type is PERIOD(TIMESTAMP[(n)] WITH TIME ZONE), both result time zone fields are set to the current session time zone displacement. An exception to this is if the source ending bound is the maximum DATE value; in that case, the result ending bound is set to the maximum TIMESTAMP value.

    PERIOD(TIME) to PERIOD(TIME)

    A PERIOD(TIME(n) [WITH TIME ZONE]) value can be cast as PERIOD(TIME[(n)] [WITH TIME ZONE]) using the CAST function.

    The UTC value of the source is copied to the UTC value in the result. If the target type specifies WITH TIME ZONE and the source contains time zones, the time zone displacements from the source are copied to the corresponding result elements. If the source does not contain time zones, the current session time zone displacement is copied to both result elements. For example, assume the current session time zone displacement is INTERVAL ‑ "08:00" HOUR TO MINUTE and the source PERIOD(TIME(0) WITH TIME ZONE) has the value PERIOD '(12:12:12+08:00, 12:12:13+08:00)'. The UTC value of this source is ('04:12:12', '04:12:13'). The UTC value of the result is set to this value. On output of this result, the UTC value is adjusted to the current session time zone and the result is ('20:12:12', '20:12:13').

    Note: This value is actually for a previous day and, assuming that the CURRENT_DATE at UTC is DATE '2006-07-28', the output beginning bound would be '2006-07-27 20:12:12' if it was a timestamp element.

    If the target precision is higher than the source precision, trailing zeros are appended to the fractional seconds. If the target precision is lower than the source precision, an error is reported.

    PERIOD(TIME) to PERIOD(TIMESTAMP)

    A PERIOD(TIME(n) [WITH TIME ZONE]) value can be cast as PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]) using the CAST function.

    The source time values get adjusted with respect to the session time zone displacement from the corresponding UTC value. The date portion of each result element is set to CURRENT_DATE. The hour, minute, and, second are copied from the source after the above adjustment and the timestamp value is converted to corresponding UTC value.

    If the target type specifies WITH TIME ZONE and the source contains time zones, the time zone displacements from the source are copied to the corresponding result elements. If the source does not contain time zones, the current session time zone displacement is copied to both result elements.

    If the target precision is higher than the source precision, trailing zeros are appended to the fractional seconds. If the target precision is lower than the source precision, an error is reported.

    PERIOD(TIMESTAMP) to PERIOD(DATE)

    A PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) value can be cast as PERIOD(DATE) using the CAST function.

    The result elements are each set to the date portion of the corresponding source bound after the source bound is adjusted according to the current session time zone (the adjustment is not done for the source ending bound if it is the maximum value). If the adjustment for time zone changes the date, the changed value is used. If the result date portions are the same, an error is reported.

    PERIOD(TIMESTAMP) to PERIOD(TIME)

    A PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) value can be cast as PERIOD(TIME[(n)] [WITH TIME ZONE]) using the CAST function.

    The date portion in the beginning and ending UTC values of the source must have the same DATE value. Otherwise, an error is reported. The time portions of the result elements are copied from the corresponding source time portions. If the target type specifies WITH TIME ZONE and the source also contains time zones, the source time zone displacements are copied to the corresponding result elements. If the source does not contain time zones, the current session time zone displacement is copied to both result elements.

    If the target precision is higher than the source precision, trailing zeros are added to the fractional seconds. If the target precision is lower than the source precision, an error is reported.

    PERIOD(TIMESTAMP) to PERIOD(TIMESTAMP)

    A PERIOD(TIMESTAMP(n) [WITH TIME ZONE]) value can be cast as PERIOD(TIMESTAMP[(n)] [WITH TIME ZONE]) using the CAST function.

    The result date and time portions are set to the corresponding source date and time portions. If the target type specifies WITH TIME ZONE and the source also contains time zones, the time zone displacements in the source are copied to the corresponding result elements. If the source does not contain time zones, the current session time zone displacement is copied to both result elements except if the source ending bound is the maximum value, the time zone for the result ending bound is +00:00.

    If the target precision is higher than the source precision, trailing zeros are added in the fractional seconds. If the target precision is lower than the source precision, an error is reported.

    Example : PERIOD(DATE) to PERIOD(TIMESTAMP)

    Assume p is a PERIOD(DATE) column in table t1 with a value of PERIOD '(2005-02-02, 2006-02-03)' and the current session time zone displacement is INTERVAL -'08:00' HOUR TO MINUTE.

    In the following example, a PERIOD(DATE) column is cast as PERIOD(TIMESTAMP(6)). The date portion is obtained from the source for the corresponding result element and the time portions are set to zero.

       SELECT CAST(p AS PERIOD(TIMESTAMP(6))) FROM t1;

    The following is returned:

       ('2005-02-02 00:00:00.000000', '2006-02-03 00:00:00.000000')

    Example : Least Significant Field in Source Lower Than Target

    Assume p is a PERIOD(TIME(2)) column in table t with a value of PERIOD '(12:12:12.45, 13:12:12.67)' and the current session time zone displacement is INTERVAL -'08:00' HOUR TO MINUTE.

    In the following example, a PERIOD(TIME(2)) column is cast as PERIOD(TIME(6) WITH TIME ZONE). The time portion is obtained from the source with trailing zeros added to the fractional seconds to make the precision 6 for the corresponding result element and both result time zone fields are set to the current session time zone displacement.

       SELECT CAST(p AS PERIOD(TIME(6)WITH TIME ZONE)) FROM t;

    The following is returned:

       ('12:12:12.450000-08:00', '13:12:12.670000-08:00')

    Related Topics

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