|
Caché 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(interval-type,integer-exp,timestamp-exp)}
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
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).
-
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.
-
-
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.
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.