SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-45
Interval Value Expressions
If the interval expression is the difference or sum of interval operands, the interval
qualifiers of the operands are either year-month or day-time. If you are updating or
inserting a value that is the result of adding or subtracting two interval qualifiers, the
interval qualifier of the result depends on the interval qualifier of the target column.
SQL/MP Considerations for Interval Value Expressions
FRACTION-Only Interval Columns
Suppose that an SQL/MP table has an INTERVAL column defined as:
MPDateTimeCol INTERVAL FRACTION(6)
DEFAULT INTERVAL '123456' FRACTION(6)
You cannot use this column in an interval expression, as a CAST argument, or as an
argument of an aggregate function such as MIN or MAX. NonStop SQL/MX returns an
error indicating that operations with FRACTION-only columns are not supported.
Considerations for Interval Value Expressions
Start and End Fields
Within the definition of an interval range, the start-field and end-field can be
any of the specified fields with these restrictions:
•
An interval is either year-month or day-time. If the start-field is YEAR, the
end-field is MONTH; if the start-field is DAY, HOUR, or MINUTE, the end-
field is also a time field.
•
The start-field must precede the end-field within the hierarchy YEAR,
MONTH, DAY, HOUR, MINUTE, and SECOND.
Within the definition of an interval expression, the start-field and end-field of
all operands in the expression must be either year-month or day-time.
Interval Qualifier
The rules for specifying the interval qualifier of the result expression vary. For example,
interval value expressions include:
CURRENT_DATE
- start_date
By default, the interval difference between the current date
and the value in column START_DATE is expressed in
days. You are not required to specify the interval qualifier.
INTERVAL '3' DAY
- INTERVAL '2' DAY
The difference of two interval literals. The result is 1 day.
INTERVAL '3' DAY
+ INTERVAL '2' DAY
The sum of two interval literals. The result is 5 days.
INTERVAL '2' YEAR
- INTERVAL '3' MONTH
The difference of two interval literals. The result is 1 year, 9
months.