INTERVAL-to-INTERVAL Conversion - Teradata Database

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.