Neoview SQL Reference Manual (R2.3)

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:
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
TimeTime – Interval
TimestampTimestamp + Interval or Interval + Timestamp
224 SQL Language Elements