Caché SQL Reference
[Back] [Next]
Go to:

A scalar date/time function that returns a new timestamp calculated by adding a number of intervals of a specified type to a specified timestamp.
{fn TIMESTAMPADD(interval-type,integer-exp,timestamp-exp)}
interval-type The type of time/date interval that integer-exp represents, specified as a keyword.
integer-exp An integer value expression that is to be added to timestamp-exp.
timestamp-exp A TIMESTAMP value expression, which will be increased by the value of integer-exp.
The TIMESTAMPADD function modifies a date/time expression by incrementing the specified date part by the specified number of units. For example, if interval-type is SQL_TSI_MONTH and integer-exp is 5, TIMESTAMPADD increments timestamp-exp by five months. You can also decrement a date part by specifying a negative integer for integer-exp. The calculated date is returned as a TIMESTAMP. You can increment or decrement by fractional seconds, counted in thousandths of a second (.001).
Note that TIMESTAMPADD can only be used as an ODBC scalar function (with the curly brace syntax).
Similar time/date modification operations can be performed on a timestamp using the DATEADD general function.
Interval Types
The interval-type argument can be one of the following timestamp intervals:
These timestamp intervals may be specified with or without enclosing double quotation marks: SQL_TSI_WEEK or "SQL_TSI_WEEK".
Incrementing or decrementing a timestamp interval causes other intervals to be modified appropriately. For example, incrementing the hour past midnight automatically increments the day, which may in turn increment the month, and so forth. TIMESTAMPADD always returns a valid date, taking into account the number of days in a month, and calculating for leap year. For example, incrementing January 31 by one month returns February 28 (the highest valid date in the month), unless the specified year is a leap year, in which case it returns February 29.
DATEADD and TIMESTAMPADD handle quarters (3-month intervals); DATEDIFF and TIMESTAMPDIFF do not handle quarters.
Timestamp Format
The timestamp-exp argument value has the same logical format and external format: a string of the form:
yyyy-mm-dd hh:mm:ss
For this argument:
Range and Value Checking
TIMESTAMPADD performs the following checks on input values. If a value fails a check, the null string is returned.
The following example adds 1 week to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_WEEK,1,'2003-12-20 12:00:00')}
it returns 2003-12-27 12:00:00, because adding 1 week adds 7 days.
The following example adds 5 months to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_MONTH,5,'1999-12-20 12:00:00')}
returns 2000-05-20 12:00:00 because in this case adding 5 months also increments the year.
The following example also adds 5 months to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_MONTH,5,'1999-01-31 12:00:00')}
it returns 1999-06-30 12:00:00. Here TIMESTAMPADD modified the day value as well as the month, because simply incrementing the month would result in June 31, which is an invalid date.
The following example increments the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,45,'1999-12-20 00:00:00')}
returns 1999-12-20 00:45:00.
The following example decrements the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,-45,'1999-12-20 00:00:00')}
it returns 1999-12-19 23:15:00. Note that in this case decrementing the time also decremented the day.
See Also