HP 3000 Manuals HP 3000 Manuals
Add Months Function
[REV BEG]
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').[REV END]
Scope
SQL Data Manipulation Statements
SQL Syntax
[REV BEG]
{[+] IntegerValue }
{[-] }
{ }
ADD_MONTHS (DateExpression, {: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.[REV END]
HostVariable is a host variable of type INTEGER. It can be
positive or negative. If negative, the absolute
value is subtracted from Value1.
IndicatorVariableames 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
[REV BEG]
* 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.
[REV END]
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)