Neoview SQL Reference Manual (R2.4)

the result of adding or subtracting two interval qualifiers, the interval qualifier of the result
depends on the interval qualifier of the target column.
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 determining the interval qualifier of the result expression vary. For example, interval
value expressions include:
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.
CURRENT_DATE - start_date
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 '3' DAY + INTERVAL '2' DAY
The difference of two interval literals. The result is 1 year,
9 months.
INTERVAL '2' YEAR - INTERVAL '3' MONTH
Restrictions on Operations
You can use datetime and interval operands with arithmetic operators in an interval value
expression only in these combinations:
Result TypeOperand 2OperatorOperand 1
IntervalDatetime–Datetime
IntervalInterval+ or –Interval
IntervalNumeric* or /Interval
IntervalInterval*Numeric
This table lists valid combinations of datetime and interval arithmetic operators, and the data
type of the result:
Result typeOperands
DateDate + Interval or Interval + Date
DateDate + Numeric or Numeric + Date
DateDate - Numeric
DateDate – Interval
IntervalDate – Date
TimeTime + Interval or Interval + Time
TimeTime + Numeric or Numeric + Time
TimeTime - Number
Expressions 247