SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
E-27
Examples—Expressions

In this example and in all these examples, date-time and INTERVAL values are
from this table:
Table Definition:
CREATE TABLE PROJECTS
( PROJECT_NAME PIC X(10) NOT NULL ,
START_DATE DATETIME YEAR TO MINUTE NOT NULL ,
END_DATE DATETIME YEAR TO MINUTE NOT NULL ,
WAIT_TIME INTERVAL DAY(2) NOT NULL )
Table Data:
PROJECT_NAME START_DATE END_DATE WAIT_TIME
------------ ---------------- ---------------- ---------
920 1988-02-21:20:30 1989-03-21:20:30 20
134 1970-01-01:00:00 1978-03-21:20:30 30
922 1940-02-21:12:30 1941-03-21:20:30 13
955 1990-10-14:14:30 1991-01-20:12:30 14

This example adds an INTERVAL value to a DATETIME value. The result is 1942-
03-21:20:30.
>> SELECT END_DATE + INTERVAL "1" YEAR
+> FROM PROJECTS WHERE PROJECT_NAME = "922";

This example subtracts an INTERVAL value qualified by MONTH from a
DATETIME value. The result is 1990-12-20:12:30. The YEAR value is
decremented by 1 because subtracting a month from January 20 causes the date
to be in the previous year.
>> SELECT END_DATE - INTERVAL "1" MONTH
+> FROM PROJECTS WHERE PROJECT_NAME = "955";

This example adds an INTERVAL value qualified by DAY to a DATETIME value.
SQL handles 1988 as a leap year. The result is 1988-03-12:20:30.
>> SELECT START_DATE + WAIT_TIME
+> FROM PROJECTS WHERE PROJECT_NAME = "920";

This example subtracts an INTERVAL value from a DATETIME value and adjusts
the adjacent field. The result is 1940-02-20:21:00.
>> SELECT START_DATE - INTERVAL "15:30" HOUR TO MINUTE
+> FROM PROJECTS WHERE PROJECT_NAME = "922";

This example adds two INTERVAL values:
>> INSERT INTO PROJECTS
+> (PROJECT_NAME, START_DATE, END_DATE, WAIT_TIME)
+> VALUES ( "945", DATE "1989-10-20" ,
+> DATE "1990-10-21" ,
+> INTERVAL "30" DAY + INTERVAL "3" HOUR) ;
Because the receiving field has DAY as its range of DATETIME fields, the result of
adding 30 days and 3 hours is expressed as 30 days. For the HOUR value to