SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Functions and Expressions

HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-004

8-46

Return type

The return type is same as the datetime_expression type, but if the

datetime_expression consists of any time components, a timestamp is returned.

For example:

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

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

Examples of DATEADD

The following example illustrates the use of DATEADD function:

>>select dt_ts, dateadd(week, 5, dt_ts) from T01;

DT_TS (EXPR)

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

2008-11-12 03:03:03.000000 2008-12-17 03:03:03.000000

2112-01-30 09:05:00.000000 2112-03-05 09:05:00.000000

--- 2 row(s) selected.

DATEDIFF Function

The DATEDIFF function returns the integer number of datepart unit boundaries

crossed between the startdate and enddate.

datepart

represents YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK,

or one of the following abbreviations:

DATEDIFF (datepart, startdate, enddate)

YEAR YY and YYYY

MONTH M and MM

DAY D and DD

HOUR HH

MINUTE MI