Normalization of Intervals with Multiple Fields - Teradata Vantage

The Parser normalizes multiple-field INTERVAL values in such a way that the defined precision for an INTERVAL value may not be large enough to contain the normalized value.

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'. Trying to insert that value into a column that has a 2-digit YEAR field fails, because the value is a 3-digit year.

The following example violates the permissible range values for the type, and therefore returns an error.

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

The Parser normalizes the value '999-12' to '1000-00' YEAR TO MONTH. Because the value for year is then four digits, an overflow occurs and the operation fails.