17.10 - Non-Intuitive Results of ADD_MONTHS - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1211-171K
Language
English (United States)

Because of the normalization made by ADD_MONTHS, many results of the function are not intuitive, and their inversions are not always symmetrical. For example, compare the results of Example: Querying for the Current Date Plus One Month with the results of Example: Querying for the Month Prior to the Current Date.

This is because the function always produces a valid date, but not necessarily an expected date. Correctness in the case of interval month arithmetic is a relative term. Any definition is arbitrary and cannot be generalized, so the word ‘expected’ is a better choice for describing the behavior of ADD_MONTHS.

The following SELECT statements return dates that are both valid and expected:

SELECT ADD_MONTHS ('1999-08-15' , 1);

This statement returns 1999-09-15.

SELECT ADD_MONTHS ('1999-09-30' , -1);

This statement returns 1999-08-30.

The following SELECT statement returns a valid date, but its ‘correctness’ depends on how you choose to define the value ‘one month.’

SELECT ADD_MONTHS ('1999-08-31' , 1);

This statement returns 1999-09-30, because September has only 30 days and the non-normalized answer of 1999-09-31 is not a valid date.