Neoview SQL Reference Manual (R2.5)

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:
Result Data TypeDescriptionDatetime Expression
INTERVAL DAY (12)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
INTERVAL DAY (3)The difference of two interval literals.
The result is 1 day.
INTERVAL '3' DAY - INTERVAL
'2' DAY
INTERVAL DAY (3)The sum of two interval literals. The
result is 5 days.
INTERVAL '3' DAY + INTERVAL
'2' DAY
INTERVAL YEAR (3) TO MONTHThe 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
262 SQL Language Elements