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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-46
Interval Value Expressions
Restrictions on Operations
You can use datetime and interval operands with arithmetic operators in an interval
value expression only in these combinations:
This table lists valid combinations of datetime and interval arithmetic operators, and the
data type of the result:
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
Operand 1 Operator Operand 2 Result Type
Datetime – Datetime Interval
Interval + or – Interval Interval
Interval * or / Numeric Interval
Numeric * Interval Interval
Operands Result type
Date + Interval or
Interval + Date
Date
Date – Interval Date
Date – Date Interval
Time + Interval or
Interval + Time
Time
Time – Interval Time
Timestamp + Interval or
Interval + Timestamp
Timestamp
Timestamp – Interval Timestamp
year-month Interval + year-month Interval year-month Interval
day-time Interval + day-time Interval day-time Interval
year-month Interval – year-month Interval year-month Interval
day-time Interval – day-time Interval day-time Interval
Time – Time Interval
Timestamp – Timestamp Interval
Interval * Number or
Number * Interval
Interval
Interval / Number Interval
Interval – Interval or
Interval + Interval
Interval