Neoview SQL Reference Manual (R2.4)

Result typeOperands
TimeTime – Interval
TimestampTimestamp + Interval or Interval + Timestamp
TimestampTimestamp + Numeric or Numeric + Timestamp
TimestampTimestamp - Numeric
TimestampTimestamp – Interval
year-month Intervalyear-month Interval + year-month Interval
day-time Intervalday-time Interval + day-time Interval
year-month Intervalyear-month Interval – year-month Interval
day-time Intervalday-time Interval – day-time Interval
IntervalTime – Time
IntervalTimestamp – Timestamp
IntervalInterval * Number or Number * Interval
IntervalInterval / Number
IntervalInterval – Interval or Interval + Interval
When using these operations, note:
• If you subtract a datetime value from another datetime value, both values must have the
same data type. To get this result, use the CAST expression. For example:
CAST (ship_timestamp AS DATE) - start_date
• If you subtract a datetime value from another datetime value, and you specify the interval
qualifier, you must allow for the maximum number of digits in the result for the precision.
For example:
(CURRENT_TIMESTAMP - ship_timestamp) DAY(4) TO SECOND(6)
• If you are updating a value that is the result of adding or subtracting two interval values,
an SQL error occurs if the source value does not fit into the target column's range of interval
fields. For example, this expression cannot replace an INTERVAL DAY column:
INTERVAL '1' MONTH + INTERVAL '7' DAY
• If you multiply or divide an interval value by a numeric value expression, Neoview SQL
converts the interval value to its least significant subfield and then multiplies or divides it
by the numeric value expression. The result has the same fields as the interval that was
multiplied or divided. For example, this expression returns the value 5-02:
INTERVAL '2-7' YEAR TO MONTH * 2
Examples of Interval Value Expressions
The PROJECT table consists of five columns using the data types NUMERIC, VARCHAR, DATE,
TIMESTAMP, and INTERVAL DAY. Suppose that you have inserted values into the PROJECT
table. For example:
INSERT INTO persnl.project
VALUES (1000,'SALT LAKE CITY',DATE '2007-04-10',
TIMESTAMP '2007-04-21:08:15:00.00',INTERVAL '15' DAY);
The next example uses these values in the PROJECT table:
EST_COMPLETESHIP_TIMESTAMPSTART_DATEPROJCODE
152007-04-21:08:15:00.00002007-04-101000
302007-07-21:08:30:00.00002007-06-102000
248 SQL Language Elements