NonStop SQL/MP Reference Manual

NonStop SQL/MP Reference Manual—142115
E-30
Examples—Expressions
•
The following 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";
•
The following 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";
•
The following 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 appear,
the WAIT_TIME column must be defined with the range DAY TO HOUR. The
inserted row is:
945 1989-10-20:00:00 1990-10-21:00:00 30
•
The following expression doubles an INTERVAL value. The result is 5 years, 2
months.
INTERVAL “2-7” YEAR TO MONTH * 2
•
The following expression divides an INTERVAL value by another. The result is 36.
INTERVAL value:
INTERVAL "3" DAY / INTERVAL "2" HOUR