InterSystems SQL Reference
TIMESTAMPADD


A scalar date/time function that returns a new timestamp calculated by adding a number of intervals of a specified date part to a timestamp.
Synopsis
{fn TIMESTAMPADD(intervaltype,integerexp,timestampexp)}
Arguments
Description
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.
TIMESTAMPADD returns a timestamp of the same data type as the input
timestampexp. This timestamp can be in either
%Library.TimeStamp data type format (yyyymmdd hh:mm:ss.ffff) or
%Library.PosixTime data type format (an encoded 64bit signed integer).
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 intervaltype argument can be one of the following timestamp intervals:

SQL_TSI_FRAC_SECOND

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_QUARTER

SQL_TSI_YEAR
These timestamp intervals may be specified with or without enclosing quotation marks, using single quotes or double quotes. They are not casesensitive.
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.
You can increment or decrement by fractional seconds of three digits of precision. Specify fractional seconds as an integer count of thousandths of a second (001 through 999).
DATEADD and TIMESTAMPADD handle quarters (3month intervals); DATEDIFF and TIMESTAMPDIFF do not handle quarters.
%TimeStamp Format
If the
timestampexp argument is in
%Library.TimeStamp data type format (yyyymmdd hh:mm:ss.ffff) the following rules apply:

If timestampexp specifies only a time 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.

The timestampexp can include or omit fractional seconds. The timestampexp can include any number of digits of precision, but intervaltype SQL_TSI_FRAC_SECOND specifies exactly three digits of precision. Attempting to specify a SQL_TSI_FRAC_SECOND of less than or more than three digits can have unpredictable results.
Range and Value Checking

All specified parts of the timestampexp must be valid before any TIMESTAMPADD operation can be performed.

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. An invalid date value results in an SQLCODE 400 error.

Date values must be within a valid range. Years: 1841 through 9999. Months: 1 through 12. Days: 1 through 31. Hours: 00 through 23. Minutes: 0 through 59. Seconds: 0 through 59. 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. An invalid date value results in an SQLCODE 400 error.

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

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. Date values less than 10 are always returned with a leading zero.

Time values may be wholly or partially omitted. If timestampexp specifies an incomplete time, zeros are supplied for the unspecified parts.

An hour value less than 10 must include a leading zero. Omitting this leading zero results in an SQLCODE 400 error.
Examples
The following example adds 1 week to the original timestamp:
SELECT {fn TIMESTAMPADD(SQL_TSI_WEEK,1,'20171220 12:00:00')}
it returns 20171227 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,'20171220 12:00:00')}
returns 20180520 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,'20180131 12:00:00')}
it returns 20180630 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,'20171220 00:00:00')}
returns 20171220 00:45:00.
The following example decrements the original timestamp by 45 minutes:
SELECT {fn TIMESTAMPADD(SQL_TSI_MINUTE,45,'20171220 00:00:00')}
it returns 20171219 23:15:00. Note that in this case decrementing the time also decremented the day.
See Also
Content Date/Time: 20190919 06:56:35