ADD_MONTHS

The ADD_MONTHS function returns a date or timestamp that represents the first argument plus a specified number of months.

Read syntax diagramSkip visual syntax diagramADD_MONTHS(expression ,numeric-expression)
expression
An expression that specifies the starting date. The expression must return a value that is of one of the following built-in data types: a date, a timestamp, a character string, or a graphic string.

If expression is a character or graphic string, its value must be a valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

numeric-expression
An expression that specifies the number of months to add to expression. numeric-expression must return a value that is a built-in numeric data type. If the data type of the expression is not BIGINT, it is implicitly cast to BIGINT before evaluating the function. A negative numeric value can be used to subtract months.

If expression is a timestamp, the result of the function is a timestamp with the same precision as expression. Otherwise, the result of the function is a date. If either argument can be null, the result can be null; if either 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, then the result is the last day of the resulting month. Otherwise, the result has the same day component as expression.

Example

  • 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.

  • 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.

  • 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.