Caché SQL Reference
TIMESTAMPADD
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   
Search:    

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(interval-type,integer-exp,timestamp-exp)}
Arguments
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.
Description
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.
TIMESTAMPADD returns a timestamp of the same data type as the input timestamp-exp:%Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff).
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 quotation marks, using single quotes or double quotes. They are not case-sensitive.
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 (3-month intervals); DATEDIFF and TIMESTAMPDIFF do not handle quarters.
%TimeStamp Format
If the timestamp-exp argument is in %Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) the following rules apply:
Range and Value Checking
TIMESTAMPADD performs the following checks on %Library.TimeStamp input values:
Examples
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