The following examples show how the results of an ADD_MONTHS function are not always what you expect when the value for DAY in date_expression or the date component of timestamp_expression is 29, 30, or 31.
All examples use a date_expression for simplicity. In every case, the function behaves as designed.
Example: Querying When Date is Invalid
The result of the SELECT statement in this example is a date in February, 1996. Because February 31 is not a valid date, ADD_MONTHS normalizes the answer.
That answer, because the DAY value in the source date is greater than the last DAY value for the target month, is the last valid DAY value for the target month.
SELECT ADD_MONTHS ('1995-12-31', 2);
The result of this example is 1996-02-29.
1996 was a leap year. If the interval is 14 months rather than 2, the result is ' 1997-02-28 '.
Example: Querying for Month before Current Date
This statement performs the converse of the ADD_MONTHS function in Example: Querying for the Current Date Plus One Month. The statement does not return '1999-01-30', the source date in that example.
SELECT ADD_MONTHS ('1999-02-28' , -1);
ADD_MONTHS returns the result 1999-01-28, which is unexpected but not an error.
Example: Querying for Current Date Plus One Month for Month Ending on 28th
The following statement does not return '1999-03-31'.
SELECT ADD_MONTHS ('1999-02-28' , 1);
ADD_MONTHS returns the result 1999-03-28.
Example: Querying for Current Date Plus One Month for Month Ending on 30th
The following statement does not return '1999-03-31'.
SELECT ADD_MONTHS ('1999-04-30' , -1);
ADD_MONTHS returns the result 1999-03-30.
Example: Querying for Current Date Plus One Month for Month Ending on 30th
The following statement does not return ' 1999-05-31 '.
SELECT ADD_MONTHS ('1999-04-30' , 1);
ADD_MONTHS returns the result 1999-05-30.