NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-28
Considerations—Expressions
a. In a date-time or INTERVAL expression, you can specify fields for the result
with a range of fields following the expression. For example, the following
expression gives the result 09-17:
( DATE "1988-09-22" - INTERVAL "5" DAY ) MONTH TO DAY
b. If you subtract a date-time value from another date-time value, both values must
have the same range of date-time field.
c. Adding an INTERVAL of MONTHS to a DATE value results in a value of the
same day plus the specified number of months. Because different months have
different lengths, this is an approximate result.
d. Date-time and INTERVAL arithmetic that involves MONTH and DAY fields
can yield unexpected results, depending on how the fields are used. For
example, the following 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, the following 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.