ADD_MONTHS scalar function

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

The schema is SYSIBM.

expression: An expression that specifies the starting date. expression must return a value that is a date, timestamp, or a valid string representation of a date or timestamp. A string representation is a value that is a built-in character string data type or graphic string data type, that is not a LOB, and that has an actual length that is not greater than 255 bytes. A time zone in a string representation of a timestamp is ignored. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.
If expression is a TIMESTAMP WITH TIME ZONE value, expression is first cast to a TIMESTAMP WITHOUT TIME ZONE value with the same precision as expression. If expression is a string, expression is first cast to DATE.
numeric-expression: An expression that returns a value of any built-in numeric data type. The integer portion of numeric-expression specifies the number of months to add to the starting date specified by expression.A negative numeric value is allowed.
numeric-expression can also be a character string or graphic string data type. The string input is implicitly cast to a numeric value of DECFLOAT(34).

If expression is a timestamp with a time zone value, or a valid string representation of a timestamp with a time zone value, the result is determined from the UTC representation of the datetime value.

If expression is a timestamp value the result is a TIMESTAMP WITHOUT TIME ZONE with the same precision as expression. Otherwise, the result is a DATE value.

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.

The result CCSID is the appropriate CCSID of the argument encoding scheme and the result subtype is the appropriate subtype of the CCSID.

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

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

Example 3: It is possible to achieve similar results with the ADD_MONTHS function and date 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–29'.

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.

Example 4: Assume TSZ is an SQL variable with the timestamp with time zone value 2008-02-29.20.00.00.000000-08.00. Set TIMESZ to the value of that timestamp with time zone value plus 4 months.

SET TIMESZ: = ADD_MONTHS(TIMESTAMP_TZ(TSZ), 4);

The function returns a timestamp value that represents the timestamp plus 4 months: 2008-06-30-20.00.00.000000-8.00. The result of the ADD_MONTHS function does not contain a time zone.

Example 5: Assume TSZ is a host variable with the value '2008-02-29-20.00.000000-08.00', which is a string representation of a timestamp with a time zone value. Set TIMESZ to the value of that timestamp with a time zone plus 4 months.

SET TIMESZ: = ADD_MONTHS(:TSZ, 4);

With the string representation of a timestamp as input, the ADD_MONTHS function returns a DATE value that represents the timestamp plus 4 months, 2008-06-30. The host variable TIMESZ is set with the value that represents the timestamp with time zone plus 4 months.