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

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-42
Datetime Value Expressions
•
Add an interval value qualified by YEAR to a datetime value:
SELECT start_date + INTERVAL '1' YEAR
FROM persnl.project
WHERE projcode = 1000;
(EXPR)
----------
1997-04-10
--- 1 row(s) selected.
•
Subtract an interval value qualified by MONTH from a datetime value:
SELECT ship_timestamp - INTERVAL '1' MONTH
FROM persnl.project
WHERE projcode = 134;
(EXPR)
--------------------------
1996-12-01 00:00:00.000000
--- 1 row(s) selected.
The result is 1996-12-01 00:00:00.00. The YEAR value is decremented by 1
because subtracting a month from January 1 causes the date to be in the previous
year.
•
Add a column whose value is an interval qualified by DAY to a datetime value:
SELECT start_date + est_complete
FROM persnl.project
WHERE projcode = 920;
(EXPR)
----------
1996-03-12
--- 1 row(s) selected.
The result of adding 20 days to 1996-02-21 is 1996-03-12. NonStop SQL/MX
correctly handles 1996 as a leap year.
•
Subtract an interval value qualified by HOUR TO MINUTE from a datetime value:
SELECT ship_timestamp - INTERVAL '15:30' HOUR TO MINUTE
FROM persnl.project
WHERE projcode = 1000;
(EXPR)
--------------------------
1996-04-20 16:45:00.000000
The result of subtracting 15 hours and 30 minutes from 1996-04-21 08:15:00.00 is
1996-04-20 16:45:00.00.