Supplement to the HP Neoview SQL Reference Manual (R2.3 SP2)

3 SQL Functions
This chapter describes these two new SQL functions:
• “TIMESTAMPADD Function” (page 17)
• “TIMESTAMPDIFF Function” (page 18)
TIMESTAMPADD Function
The TIMESTAMPADD function adds the interval of time specified by interval-ind and
num_expr to datetime_expr. If the specified interval is in years, months, or quarters and the
resulting date is not a valid date, the day will be rounded down to the last day of the result
month. The type of the datetime_expr is returned except when the interval-ind contains
any time component, in which case a TIMESTAMP is returned.
TIMESTAMPADD is a Neoview extension.
TIMESTAMPADD (interval-ind, num_expr, datetime_expr)
interval-ind
is SQL_TSI_YEAR, SQL_TSI_MONTH, SQL_TSI_DAY, SQL_TSI_HOUR, SQL_TSI_MINUTE,
SQL_TSI_SECOND, SQL_TSI_QUARTER, or SQL_TSI_WEEK
num_expr
is an SQL exact numeric value expression that specifies how many interval-ind units of
time are to be added to datetime_expr. If num_expr has a fractional portion, it is ignored.
If num_expr is negative, the return value precedes datetime_expr by the specified amount
of time.
datetime_expr
is an expression that evaluates to a datetime value of type DATE or TIMESTAMP. The type
of the datetime_expr is returned except when the interval-ind contains any time
component, in which case a TIMESTAMP is returned.
Examples of TIMESTAMPADD
• This function adds seven days to the date specified in start-date:
TIMESTAMPADD (SQL_TSI_DAY, 7, start-date)
• This function returns the value DATE '2008–03–06':
TIMESTAMPADD (SQL_TSI_WEEK, 1, DATE '2008-02-28')
• This function returns the value DATE '1999–02–28':
TIMESTAMPADD (SQL_TSI_YEAR, -1, DATE '2000-02-29')
• This function returns the value TIMESTAMP '2003–02–28 13:27:35':
TIMESTAMPADD (SQL_TSI_MONTH, -12, TIMESTAMP '2004-02-29 13:27:35')
• This function returns the value TIMESTAMP '2004–02–28 13:27:35':
TIMESTAMPADD (SQL_TSI_MONTH, 12, TIMESTAMP '2003-02-28 13:27:35')
• This function returns the value DATE '2008–06–30':
TIMESTAMPADD (SQL_TSI_QUARTER, -2, DATE '2008-12-31')
• This function returns the value TIMESTAMP '2008–06–30 23:59:55':
TIMESTAMPADD (SQL_TSI_SECOND, -5, DATE '2008-07-01')
TIMESTAMPADD Function 17