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

SQL Date and Time Functions and Expressions

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
dzx1591742169550.ditamap
dita:ditavalPath
dzx1591742169550.ditaval
dita:id
B035-1211
lifecycle
previous
Product Category
Teradata Vantage™

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.