Add Months Function

The Add Months function uses the keyword ADD_MONTHS to apply the addition operation to a DATE or DATETIME expression. It is different from a simple addition operator in that it adjusts the day field in the DATE or DATETIME value to the last day of the month if adding the months creates an invalid date (such as '1989-02-30').

Scope

SQL Data Manipulation Statements

SQL Syntax

ADD_MONTHS (DateExpression, {[+ 
                              -]IntegerValue
                             :HostVariable [[INDICATOR]:IndicatorVariable]
                             ?
                             :LocalVariable
                             :ProcedureParameter                          })

Parameters

DateExpression

is either a DATE or DATETIME expression. See the "Expression" section of this chapter for details on the syntax.

HostVariable

is a host variable of type INTEGER. It can be positive or negative. If negative, the absolute value is subtracted from Value1.

IndicatorVariable

names an indicator variable, whose value determines whether the associated host variable contains a NULL value:

> = 0: the value is not NULL
< 0: the value is NULL (The value in the host variable will be ignored.)

?

indicates a dynamic parameter in a prepared SQL statement. The value of the parameter is supplied when the statement is executed.

LocalVariable

contains a value within a procedure.

ProcedureParameter

contains a value that is passed into or out of a procedure.

Description

The Add Months function adds a duration of months to a DATE or DATETIME expression. Only the month portion of the value is affected, and, if necessary, the year portion. The day portion of the date is unchanged unless the result would be invalid (for example, '1989-02-31'). In this case, the day is set to the last day of the month for that year, and ALLBASE/SQL generates a warning indicating the adjustment.
If either parameter is NULL, ADD_MONTHS will evaluate to NULL also.

Example

In this example, rows are returned which comprise the batch stamp and test date that have a pass quantity less than 48. A warning is generated because 7 months added to the '1984-07-30' date results in an invalid date, '1985-02-30'.

   SELECT BatchStamp, ADD_MONTHS(TestDate,7)
     FROM ManufDB.TestData
    WHERE PassQty <= 48
 
   ADD_MONTHS result adjusted to last day of month.  (DBWARN 2042)

Add Months Function

Technical documentation

» Table of Contents

» Index

Scope

SQL Syntax

Parameters

Description

Example