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.