Interval Data Type Assignment Rules | Teradata Vantage - 17.00 - 17.05 - Interval Data Type Assignment Rules - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Release Date
June 2020
Content Type
Programming Reference
Publication ID
B035-1211-170K
Language
English (United States)

The following rules apply to Year-Month INTERVAL assignments.

WHEN … THEN …
the types match assignment is straightforward.
the source is INTERVAL YEAR and the target is INTERVAL YEAR TO MONTH the value for MONTH in the target is set to zero.
the source is INTERVAL MONTH and the target is INTERVAL YEAR TO MONTH the source is extended to include the YEAR field initialized to zero, and the resulting interval is normalized.

For example, if the source is '15' then the extended source is '0-15', normalized to '1-03'.

the target is INTERVAL MONTH and the source is either INTERVAL YEAR or INTERVAL YEAR TO MONTH the source is converted to INTERVAL MONTH before assignment.

For example, if the source is '2-11', it is converted to '35'.

the least significant field of the source is lower than that of the target the values of fields in the source with precision lower than the least significant field of the target are truncated.

For example, if a source of INTERVAL '32' MONTH is assigned to a target column of type INTERVAL YEAR, the value stored is '2'.

The following rules apply to Day-Time INTERVAL assignments.

WHEN … THEN …
the types match assignment is straightforward.
the target is of lower significance than the least significant field of the source values for those fields are set to zero.

For example, if the source is INTERVAL '49:30' HOUR TO MINUTE and it is assigned to a target column of type INTERVAL HOUR(4) TO SECOND(2), the value stored is '49:30:00.00'.

the target has fields of higher significance than the most significant field of the source the source type is extended to match the target type, setting the new fields to zeros, and normalizing the content as the final step.

For example, if the source is INTERVAL '49:30' HOUR TO MINUTE and it is assigned to a target column of type INTERVAL DAY TO MINUTE, the value stored is '2 1:30'.

the least significant field of the source is lower than that of the target the values of fields in the source with precision lower than the least significant field of the target are truncated.

For example, if the source is INTERVAL '10:12:58' HOUR TO SECOND and it is assigned to a target column of type INTERVAL HOUR TO MINUTE, the value stored is '10:12'.