Interval Data Type Assignment Rules | Teradata Vantage - Interval Data Type Assignment Rules - Analytics Database - Teradata Vantage

SQL Date and Time Functions and Expressions
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-10-30
dita:mapPath
cpk1628111786971.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
pxz1544241488545
lifecycle
latest
Product Category
Teradata Vantage™

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'.