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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-47
Interval Value Expressions
•
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,
NonStop SQL/MX 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 six 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,9657,'SALT LAKE CITY',DATE '1996-04-10',
TIMESTAMP '1996-04-21:08:15:00.00',INTERVAL '15' DAY);
The next example uses these values in the PROJECT table:
•
Suppose that the CURRENT_TIMESTAMP is 2000-01-06 11:14:41.748703. Find
the number of days, hours, minutes, seconds, and fractional seconds in the
difference of the current timestamp and the SHIP_TIMESTAMP in the PROJECT
table:
SELECT projcode,
(CURRENT_TIMESTAMP - ship_timestamp) DAY(4) TO SECOND(6)
FROM samdbcat.persnl.project;
Project/Code (EXPR)
PROJCODE START_DATE SHIP_TIMESTAMP EST_COMPLETE
1000 1996-04-10 1996-04-21:08:15:00.0000 15
2000 1996-06-10 1996-07-21:08:30:00.0000 30
2500 1996-10-10 1996-12-21:09:00:00.0000 60
3000 1996-08-21 1996-10-21:08:10:00.0000 60
4000 1996-09-21 1996-10-21:10:15:00.0000 30
5000 1996-09-28 1996-10-28:09:25:01.1111 30