Neoview SQL Reference Manual (R2.4)

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')

460 SQL Functions and Expressions