Neoview SQL Reference Manual (R2.4)

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 '2007-04-10',
TIMESTAMP '2007-04-21:08:15:00.00',INTERVAL '15' DAY);
The next examples use these values in the PROJECT table:
EST_COMPLETESHIP_TIMESTAMPSTART_DATEPROJCODE
152007-04-21 08:15:00.002007-04-101000
302007-12-21 08:15:00.002007-10-20945
202007-03-12 09:45:00.002007-02-21920
302008-01-01 00:00:00.002007-11-20134
• Add an interval value qualified by YEAR to a datetime value:
SELECT start_date + INTERVAL '1' YEAR
FROM persnl.project
WHERE projcode = 1000;
(EXPR)
----------
2008-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)
--------------------------
2007-12-01 00:00:00.000000
--- 1 row(s) selected.
The result is 2007-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)
----------
2007-03-12
--- 1 row(s) selected.
The result of adding 20 days to 2008-02-21 is 2008-03-12. Neoview SQL correctly handles
2008 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)
244 SQL Language Elements