 |
» |
|
|
|
The Add Months function uses the keyword ADD_MONTHS to apply the addition operation to a DATE or DATETIME field. 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)
|
|
