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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-41
Datetime Value Expressions
In the last expression, the datetime primary is this scalar subquery:
( SELECT ship_timestamp FROM project WHERE projcode=1000 )
The preceding subquery returns a value with TIMESTAMP data type. Therefore, the
data type of the result is TIMESTAMP.
Restrictions on Operations With Datetime or Interval Operands
You can use datetime and interval operands with arithmetic operators in a datetime
value expression only in these combinations:
When using these operations, note:
•
Adding or subtracting an interval of months to a DATE value results in a value of
the same day plus or minus the specified number of months. Because different
months have different lengths, this is an approximate result.
•
Datetime and interval arithmetic can yield unexpected results, depending on how
the fields are used. For example, execution of this expression (evaluated left to
right) returns an error:
DATE '1996-01-30' + INTERVAL '1' MONTH + INTERVAL '7' DAY
In contrast, this expression (which adds the same values as the previous
expression, but in a different order) correctly generates the value 1996-03-06:
DATE '1996-01-30' + INTERVAL '7' DAY + INTERVAL '1' MONTH
Examples of Datetime Value Expressions
The PROJECT table consists of five columns that use 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 '1996-04-10',
TIMESTAMP '1996-04-21:08:15:00.00',INTERVAL '15' DAY);
The next examples use these values in the PROJECT table:
Operand 1 Operator Operand 2 Result Type
Datetime + or – Interval Datetime
Interval + Datetime Datetime
PROJCODE START_DATE SHIP_TIMESTAMP EST_COMPLETE
1000 1996-04-10 1996-04-21 08:15:00.00 15
945 1996-10-20 1996-12-21 08:15:00.00 30
920 1996-02-21 1996-03-12 09:45:00.00 20
134 1996-11-20 1997-01-01 00:00:00.00 30