The ADD_MONTHS function returns a datetime value that represents expression plus a specified number of months.

The schema is SYSIBM.

expression

An expression that specifies the starting date.
The expression must return a value of one of the following built-in data types: a DATE or a TIMESTAMP.

numeric-expression

An expression that returns a value of any built-in numeric data type.
If the value is not of type INTEGER, it is implicitly cast to INTEGER before evaluating the function.
The numeric-expression specifies the number of months to add to the starting date specified by expression.
A negative numeric value is allowed.

The result of the function has the same data type as expression, unless expression is a string, in which case the result data type is DATE.
The result can be null; if any argument is null, the result is the null value.

If expression is the last day of the month or if the resulting month has fewer days than the day component of expression, the result is the last day of the resulting month.
Otherwise, the result has the same day component as expression.
Any hours, minutes, seconds or fractional seconds information included in expression is not changed by the function.

Examples

1. Assume today is January 31, 2007.
   Set the host variable ADD_MONTH with the last day of January plus 1 month.

   SET :ADD_MONTH = ADD_MONTHS(LAST_DAY(CURRENT_DATE), 1);

   The host variable ADD_MONTH is set with the value representing the end of February, 2007-02-28.


2. Assume DATE is a host variable with the value July 27, 1965.
   Set the host variable ADD_MONTH with the value of that day plus 3 months.

   SET :ADD_MONTH = ADD_MONTHS(:DATE,3);

   The host variable ADD_MONTH is set with the value representing the day plus 3 months, 1965-10-27.


3. The ADD_MONTHS function can be used to achieve similar results as datetime arithmetic.
   The following examples demonstrate the similarities and contrasts.

   SET :DATEHV = DATE('2008-2-28') + 4 MONTHS;
   SET :DATEHV = ADD_MONTHS('2008-2-28', 4);

   In both cases, the host variable DATEHV is set with the value '2008-06-28'.
   Now consider the same examples but with the date '2008-2-29' as the argument.

   SET :DATEHV = DATE('2008-2-29') + 4 MONTHS;

   The host variable DATEHV is set with the value '2008-06-29'.

   SET :DATEHV = ADD_MONTHS('2008-2-29', 4);

   The host variable DATEHV is set with the value '2008-06-30'.
   In this case, the ADD_MONTHS function returns the last day of the month, which is June 30, 2008, instead of June 29, 2008.
   The reason is that February 29 is the last day of the month.
   So, the ADD_MONTHS function returns the last day of June.