Normalization of Intervals with Multiple Fields - Advanced SQL Engine - Teradata Database

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.