Using the Date/Time ADD_MONTHS Function

This function allows you to add an integer number of months to a DATE or DATETIME column. Do so by indicating the number of months as a positive, negative, or unsigned integer value. (An unsigned value is assumed positive.) Also, you can specify the integer in a host variable of type INTEGER.

The ADD_MONTHS function can be used in both input and output operations; refer back to Table 11-2 “Host Variable Data Type Compatibility for Date/Time Functions ”.

Following is the general syntax for the ADD_MONTHS function:

As with date/time output functions, use the ADD_MONTHS function with any DML operation listed earlier in this chapter in Table 11-1 “Where to Use Date/Time Functions”, with one exception. In the case of a [BULK] INSERT command, the ADD_MONTHS function is limited to use in the select list and the WHERE clause of a Type 2 INSERT command.

Example ADD_MONTHS Function

Perhaps you want to increment each date in the TestDate column by one month in the ManufDB.TestData table of the manufacturing database. The following command could be used:

UPDATE ManufDB.TestData SET TestDate = ADD_MONTHS (TestDate, 1);