ADD_MONTHS Function

The ADD_MONTHS function takes a DATETIME or DATE expression as its first argument, and requires a second integer argument, specifying the number of months to add to the first argument value. The second argument can be positive or negative.

The value returned is the sum of the DATE or DATETIME value of the first argument, as an INTERVAL UNITS MONTH value, based on the number of months that the second argument specifies.

The returned data type depends on the data type of the first argument:

If the first argument evaluates to a DATE value, ADD_MONTHS returns a DATE value.
If the first argument evaluates to a DATETIME value, ADD_MONTHS returns a DATETIME YEAR TO FRACTION(5) value, with the same values for time units smaller than day as in the first argument.

If the day and month time units in the first argument specify the last day of the month, or if the resulting month has fewer days than the day in the first argument, then the returned value is the last day of the resulting month. Otherwise, the returned value has the same day of the month as the first argument.

The returned value can be in a different year, if the resulting month is later than December (or for negative second arguments, earlier than January) of the year in the first argument.

The following query calls the ADD_MONTHS function twice in the Projection clause, using column expressions as arguments. Here the column names indicate the column data types, and the DBDATE setting is MDY4/:

SELECT a_serial, b_date, ADD_MONTHS(b_date, a_serial),
       c_datetime, ADD_MONTHS(c_datetime, a_serial)
   FROM mytab WHERE a_serial = 7;

In this example ADD_MONTHS returns DATE and DATETIME values:

a_serial      7
b_date        07/06/2007
(expression)  02/06/2008
c_datetime    2007-10-06 16:47:49.00000
(expression)  2008-05-06 16:47:49.00000

If you use a host variable to store the argument to ADD_MONTHS, but the data type of the argument is not known at prepare time, HCL OneDB™ assumes that the data type is DATETIME YEAR TO FRACTION(5). If at runtime, after the statement has been prepared, the user supplies a DATE value for the host variable, the database server issues error -9750. To prevent this error, specify the data type of the host variable by using a cast, as in this program fragment:

sprintf(query, “, 
   “select add_months(?::date, 6) from mytab”);
EXEC SQL prepare selectq from :query;
EXEC SQL declare select_cursor cursor for selectq;
EXEC SQL open select_cursor using :hostvar_date_input;
	
EXEC SQL fetch select_cursor into :var_date_output;