SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001

9-49

Considerations for DATE_SUB

If the specified interval is in years or months, the DATE_SUB function normalizes the

result. For example, if the end day of the resulting date is invalid, the day is rounded off

to the last day of the resulting month.

Return type

The return type is same as the type of datetime-expression, but if the

interval-expression consists of any time components, a timestamp is returned.

For example,

DATE_SUB(DATE ‘2007-02-28’, INTERVAL ‘7’ DAY) returns DATE

DATE_SUB(DATE ‘2007-02-28’, INTERVAL ‘7’ HOUR) returns TIMESTAMP

Examples of DATE_SUB

The following example illustrates the use of DATE_SUB function:

>>select dt_ts, intr_sec, date_sub(dt_date, intr_sec) from

T01;

DT_TS INTR_SEC (EXPR)

-------------------------- ---------- ---------------------

-----

2008-11-12 03:03:03.000000 7.000000 2011-11-26

23:59:53.0000002112-01-29 23:58:50.000000

2112-01-30 09:05:00.000000 70.000000 2112-01-29

23:58:50.000000

--- 2 row(s) selected.