 |
» |
|
|
|
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) |
