Normalization of Intervals with Multiple Fields - 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™

Because of the way the Parser normalizes multiple field INTERVAL values, the defined precision for an INTERVAL value may not be large enough to contain the value once it has been normalized.

For example, inserting a value of '99-12' into a column defined as INTERVAL YEAR(2) TO MONTH causes an overflow error because the Parser normalizes the value to '100-00'. When an attempt is made to insert that value into a column defined to have a 2-digit YEAR field, it fails because it is a 3-digit year.

Here is an example that returns an overflow error because it violates the permissible range values for the type.

First define the table.

CREATE TABLE BillDateTime
(column_1 INTERVAL YEAR
,column_2 INTERVAL YEAR(1) TO MONTH
,column_3 INTERVAL YEAR(2) TO MONTH
,column_4 INTERVAL YEAR(3) TO MONTH );

Now insert the value INTERVAL '999-12' YEAR TO MONTH using this INSERT statement.

INSERT BillDateTime (column_1, column_4) 
VALUES ( INTERVAL '40' YEAR, INTERVAL '999-12' YEAR TO MONTH );

The result is an overflow error because the valid range for INTERVAL YEAR(3) TO MONTH values is -'999-11' to '999-11'.

You might expect the value '999-12' to work, but it fails because the Parser normalizes it to a value of '1000-00' YEAR TO MONTH. Because the value for year is then four digits, an overflow occurs and the operation fails.