15.00 - INTERVAL-to-INTERVAL 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)

INTERVAL-to-INTERVAL Conversion

CAST Syntax

where:

 

Syntax element …

Specifies …

interval_expression

an INTERVAL expression to be converted.

interval_data_type

the target INTERVAL type to which the interval expression is to be converted.

interval_data_attribute

one of the following optional data attributes:

  • NAMED
  • TITLE
  • ANSI Compliance

    This is ANSI SQL:2011 compliant.

    As an extension to ANSI, CAST permits the use of data attribute phrases.

    Teradata Conversion Syntax

    where:

     

    Syntax element …

    Specifies …

    interval_expression

    an INTERVAL expression to be converted.

    interval_data_type

    the optional target INTERVAL type to which the interval expression is to be converted.

    data_attribute

    one of the following optional data attributes:

  • NAMED
  • TITLE
  • ANSI Compliance

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

    Compatible Types

    Both data types must be from the same INTERVAL family: either Year-Month or Day-Time. Types cannot be mixed.

     

    This INTERVAL data type …

    Belongs to this INTERVAL family …

  • INTERVAL YEAR
  • INTERVAL YEAR TO MONTH
  • INTERVAL MONTH
  • Year-Month

  • INTERVAL DAY
  • INTERVAL DAY TO HOUR
  • INTERVAL DAY TO MINUTE
  • INTERVAL DAY TO SECOND
  • INTERVAL HOUR
  • INTERVAL HOUR TO MINUTE
  • INTERVAL HOUR TO SECOND
  • INTERVAL MINUTE
  • INTERVAL MINUTE TO SECOND
  • INTERVAL SECOND
  • Day-Time

    Conversion of INTERVAL types is performed only when the fields and precisions are different.

    Precision of Source and Target Types

    A conversion can result in an overflow error if the precision of the target data type is smaller than the corresponding precision for the source data type.

    If the least significant value of the source is lower than that of the target, then those source values having lower precision than the least significant field of the target are ignored. The result is truncation. Recovery from this action is installation-dependent.

    If the most significant field in the source value has higher significance than the most significant field in the target value, then the higher order fields of the source are converted into a scalar value of the precision of the most significant field in the target, using the factors of 12 months per year, 24 hours per day and so on.

    If the compared scalar value overflows the defined precision for the target field, an error is returned.

    Implicit INTERVAL-to-INTERVAL Conversion

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

    Conversion of INTERVAL types is performed only when both data types are from the same INTERVAL family: either Year-Month or Day-Time. See “Compatible Types” on page 657.

    Example : Least Significant Field in Source Lower Than Target

    The following query converts ‘3-11’ to ‘3’. Source is INTERVAL YEAR(2). The truncation completes the conversion.

    SELECT CAST(INTERVAL '3-11' YEAR TO MONTH AS INTERVAL YEAR(2));

    Example : Least Significant Field in Source Lower Than Target

    The following query converts ‘135 12:37:25.26’ to ‘3252’. Source is DAY(3) TO SECOND(2)

    SELECT CAST(INTERVAL '135 12:37:25.26' DAY(3) TO SECOND(2) AS INTERVAL HOUR(4));

    Example : Least Significant Field in Source Higher Than Target

    The following query converts ‘3’ to ‘3-00’. Source is INTERVAL YEAR. The insertion of zeros completes the conversion.

    SELECT CAST(INTERVAL '3' YEAR AS INTERVAL YEAR TO MONTH);

    Example : Least Significant Field in Source Higher Than Target

    The following query converts ‘135 00:00:00.0’ to ‘3240:00:00.00’ after you perform the additional conversion of multiplying 135 * 24 to obtain 3240, which is the final HOUR value. The source had a data type of DAY.

    SELECT CAST(INTERVAL ' 135 00:00:00.0' DAY AS INTERVAL HOUR TO SECOND);

    Example : Most Significant Field in Source Higher Than Target

    The following query first treats the source INTERVAL value as ‘135 12’ and then computes HOURS as (135*24)+12=3252. The result of the query is INTERVAL ‘3252’ HOUR unless the precision for the target value is less than 4, in which case an error is returned. The source had a data type of DAY TO SECOND.

    SELECT CAST(INTERVAL '135 12:37:25.26' DAY TO SECOND AS INTERVAL HOUR);

    Example : Implicit Type Conversion During Assignment

    Consider the following table which has an INTERVAL YEAR TO MONTH column:

       CREATE TABLE TimeInfo
         (YrToMon INTERVAL YEAR TO MONTH);

    If you insert data into the column using the following parameterized request, and you pass an INTERVAL YEAR or INTERVAL MONTH value to the request, Teradata Database implicitly converts the value to an INTERVAL YEAR TO MONTH value before inserting the value.

       INSERT INTO TimeInfo
       VALUES (?);

    Related Topics

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