TIMESTAMPADD - Caché SQL Reference - Caché & Ensemble 2018.1

docs.intersystems.com

Caché SQL Reference

TIMESTAMPADD

InterSystems: The power behind what matters

Caché Development References > Caché SQL Reference > SQL Functions > 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(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:

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 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:

If timestamp-exp specifies only a time value, the date portion of timestamp-exp is set to '1900–01–01' before calculating the resulting timestamp.
If timestamp-exp specifies only a date value, the time portion of timestamp-exp is set to '00:00:00' before calculating the resulting timestamp.
The timestamp-exp can include or omit fractional seconds. The timestamp-exp can include any number of digits of precision, but interval-type 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

TIMESTAMPADD performs the following checks on %Library.TimeStamp input values:

All specified parts of the timestamp-exp 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 non-canonical 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 timestamp-exp 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,'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.