Use the TIMESTAMPADD function to add a number of specified intervals to a timestamp.
Format
{{ FN TIMESTAMPADD( IntervalType, Intervals, Timestamp ) }}
Remarks
The TIMESTAMP function returns a timestamp that is calculated by adding a number of Intervals of IntervalType to Timestamp. Where Intervals can be any whole number numeric expression and Timestamp is any date\time expression.
The following table specifies possible values for IntervalType:
| IntervalType | Description |
|---|---|
| SQL_TSI_FRAC_SECOND | Thousandths of a second |
| SQL_TSI_SECOND | Seconds |
| SQL_TSI_MINUTE | Minutes |
| SQL_TSI_HOUR | Hours |
| SQL_TSI_DAY | Days |
| SQL_TSI_WEEK | Weeks |
| SQL_TSI_MONTH | Months |
| SQL_TSI_QUARTER | Quarters of year |
| SQL_TSI_YEAR | Years |
{ FN TIMESTAMPADD( SQL_TSI_SECOND, 15, TIMESTAMP '2025-08-02 00:00:00' ) }
Considerations when using a MONTH interval
Take care to ensure that each query evaluates successfully, particularly when subtracting or adding whole months from valid dates. Ensure that the evaluated day is valid for that month, particularly when calculating dates in months in which the number of days vary between months. Most months have either 30 or 31 days, but February varies between 28 and 29 days. Ensure that the query evaluates successfully in each situation.
The following query will evaluate as invalid, as the next month (April) has a fewer number of days than the specified month (March):
SELECT {FN TIMESTAMPADD(SQL_TSI_MONTH, 1, DATE'2025-03-31')} FROM CRootGroup
Such an invalid query will return an invalid date, which might evaluate to MIN_DATE (that is, 1601-01-01), depending on whether ODBC is used.
Consequently, the following query will return an unexpected result of 1601-01-02:
SELECT {FN TIMESTAMPADD(SQL_TSI_MONTH, 1, DATE'2025-03-31')} + INTERVAL'1'DAY FROM CRootGroup
Where offsets of months are required, try to use a query that includes an offset from a base date at the start of the month, or from the 28th of each month. For example, the following expression will offset the start of the month in whole months and then subtract a day:
SELECT {FN TIMESTAMPADD(SQL_TSI_MONTH, 1, DATE'2025-03-01')} - INTERVAL'1'DAY FROM CRootGroup