SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-26
Examples—Expressions
this expression (evaluated left to right) generates an SQL error because the
calculation must use February 30:
DATETIME "1989-01-30" YEAR TO DAY
+ INTERVAL "1" MONTH + INTERVAL "7" DAY
In contrast, this expression (which adds the same values as the previous one,
but in a different order) generates the value 1989-03-06:
DATETIME "1989-01-30" YEAR TO DAY
+ INTERVAL "7" DAY + INTERVAL "1" MONTH
Addition or subtraction of a date-time value and an INTERVAL value results in
a DATETIME value that must be within the range of fields for the result. SQL
adjusts values in adjacent DATETIME fields if necessary.
The result of adding or subtracting an INTERVAL of n YEARS to or from a
date-time value is a value n YEARS after or before the original date-time value.
The other fields of the result remain the same.
e. Truncation occurs if the result of adding or subtracting two INTERVAL values
causes a result that does not fit in the receiving field's range of INTERVAL
fields. SQL issues a warning if this occurs.
f. If you multiply or divide an INTERVAL value by a numeric value, SQL converts
the INTERVAL value to its smallest subfield and then multiplies or divides it by
the numeric value. The range of fields in the result is the minimum range
required to contain the final result.
g. You can only divide an INTERVAL by another INTERVAL if the two INTERVAL
values are compatible. You cannot divide a year-month interval by a day-time
interval.
Examples—Expressions

These examples are of arithmetic expressions:
QTY_ON_HAND * AVG (PARTCOST) Column value multiplied by
function applied to column values
QTY_ORDERED * (PRICE - PARTCOST) Column values combined by
operators
PRICE * :INCREASE Column value multiplied by value
in host variable