SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-23
Accessing Date-Time Values
The query returns this result:
(EXPR)
--------------
1988-03-12:20:30
Subtracting an INTERVAL Value From a DATETIME Value
This example subtracts an INTERVAL value with a MONTH value from a DATETIME
value:
SELECT end_date - INTERVAL "1" MONTH
FROM projects
WHERE project_name = "955" ;
The query returns this result:
(EXPR)
--------------
1990-12-20:12:30
In this case, the YEAR value was decremented by 1 because subtracting a month from
January 20 caused the date to be in the previous year.
The next example subtracts an INTERVAL value from a DATETIME value and adjusts
the adjacent column:
SELECT start_date - INTERVAL "15:30" HOUR TO MINUTE
FROM projects
WHERE project_name = "922" ;
The query returns this result:
(EXPR)
--------------
1940-02-20:21:00
Adding Two INTERVAL Values
This expression adds two INTERVAL values:
INTERVAL "30" DAY + INTERVAL "3" HOUR
Because the receiving column (on the left) has a DAY range, the result of adding 3
hours is 30 days. To retain the HOUR value, the receiving column must be defined with
the range DAY TO HOUR; for example:
WAIT_TIME INTERVAL DAY(2) TO HOUR NO DEFAULT NOT NULL