
Caché SQL Reference
TIMESTAMPADD



Server:docs1 
Instance:LATEST 
User:UnknownUser 


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.
Synopsis
{fn TIMESTAMPADD(intervaltype,integerexp,timestampexp)}
The
TIMESTAMPADD function modifies a date/time expression by incrementing the specified date part by the specified number of units. For example, if
intervaltype is SQL_TSI_MONTH and
integerexp is 5,
TIMESTAMPADD increments
timestampexp by five months. You can also decrement a date part by specifying a negative integer for
integerexp. 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.
The
intervaltype argument can be one of the following timestamp intervals:
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.
The
timestampexp argument value has the same logical format and external format: a string of the form:

If
timestampexp specifies only a date value, the date portion of
timestampexp is set to '1900–01–01' before calculating the resulting timestamp.

If
timestampexp specifies only a date value, the time portion of
timestampexp is set to '00:00:00' before calculating the resulting timestamp.

You can include or omit fractional seconds.
TIMESTAMPADD performs the following checks on input values. If a value fails a check, the null string is returned.

A date string must be complete and properly formatted with the appropriate number of elements and digits for each element, and the appropriate separator character. Years must be specified as four digits.

Date values must be within a valid range. Years: 1841 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 0 through 23. Minutes: 0 through 59. Seconds: 0 through 59.

The incremented year value returned must be within the range 1841 through 9999. Incrementing beyond this range returns <null>.

The number of days in a month must match the month and year. For example, the date '02–29' is only valid if the specified year is a leap year.

Date values less than 10 may include or omit a leading zero. Other noncanonical integer values are not permitted. Therefore, a Day value of '07' or '7' is valid, but '007', '7.0' or '7a' are not valid.
The following example adds 1 week to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_WEEK,1,'20031220 12:00:00')}
it returns 20031227 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,'19991220 12:00:00')}
returns 20000520 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,'19990131 12:00:00')}
it returns 19990630 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,'19991220 00:00:00')}
returns 19991220 00:45:00.
The following example decrements the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,45,'19991220 00:00:00')}
it returns 19991219 23:15:00. Note that in this case decrementing the time also decremented the day.