ADD_MONTHS scalar function in DB2 for i SQL

ADD_MONTHS scalar function in DB2 for i SQL

ADD_Months

The ADD_Months function returns a date after adding specified months to it.

ADD_MONTHS(expression, numeric-expression)

Where, expression is the starting date.

numeric-expression is the number of months to add. Negative value is also allowed.

Example #1:Today is 4th March 2024. Add 1 month to this current date.

SELECT ADD_MONTHS(CURRENT_DATE, 1) FROM sysibm.sysdummy1

Output

ADD_MONTHS
 04/04/24 

Passed the expression as current date by passing the CURRENT_DATE function returns a DATE value representing the current date in local time. No input parameters accepted by this CURRENT_DATE function. In numeric-expression we passed 1 i.e. add 1 month to the current date.

Example #2:How to achieve the same result as in Example#1 without using ADD_MONTHS function.

SELECT DATE('2024-3-04') + 1 MONTHS FROM sysibm.sysdummy1

Output

Date expression
   04/04/24    

Here, We passed a date value to date function and convert it to date format and then we add 1 months to it by using plus operator. Here, DATE function accepts input parameter.

Or we can write ADD_MONTHS function like this too, where we passed a value in the date string format.

SELECT ADD_MONTHS('2024-3-04', 1) FROM sysibm.sysdummy1

ADD_MONTHS
 04/04/24 

Example #3:Passing month as minus 1 to current date in ADD_MONTHS scalar SQL function.

SELECT ADD_MONTHS('2024-3-04', -1) FROM sysibm.sysdummy1

Or

SELECT ADD_MONTHS(Current_Date, -1) FROM sysibm.sysdummy1

ADD_MONTHS
 02/04/24 

Current date was 4th march 2024 and we added minus 1 month that returns 4th February 2024.