Caché SQL Reference
TIMESTAMPDIFF


A scalar date/time function that returns an integer count of the difference between two timestamps for a specified date part.
Synopsis
{fn TIMESTAMPDIFF(intervaltype,startdate,enddate)}
The
TIMESTAMPDIFF function returns the difference between two given timestamps (that is, one timestamp is subtracted from the other) for the specified date part interval (seconds, days, weeks, etc.). The value returned is an INTEGER, the number of these intervals between the two timestamps. (If
enddate is earlier than
startdate,
TIMESTAMPDIFF returns a negative INTEGER value.)
The
intervaltype 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 casesensitive.
Note that
TIMESTAMPDIFF can only be used as an ODBC scalar function (with the curly brace syntax). Similar time/date comparison operations can be performed on a timestamp using the
DATEDIFF general function.

If either timestamp expression specifies only a time value and
intervaltype specifies a date interval (days, weeks, months, or years), the missing date portion of the timestamp defaults to '1900–01–01' before calculating the resulting interval count.

If either timestamp expression specifies only a date value and
intervaltype specifies a time interval (hours, minutes, seconds, fractional seconds), the missing time portion of the timestamp defaults to '00:00:00.000' before calculating the resulting interval count.

You can include or omit fractional seconds of any number of digits of precision. SQL_TSI_FRAC_SECOND returns a difference of fractional seconds as an integer count of thousandths of a second (three digits of precision).


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 8 error.

Date values must be within a valid range. Years: 0001 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 8 error.

Date values less than 10 (month and day) 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.

Time values may be wholly or partially omitted. If
startdate or
enddate 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 8 error.
The following example returns 7 because the second timestamp (19991220 12:00:00) is 7 months greater than the first one:
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
'1999519 00:00:00','19991220 12:00:00')}
The following example returns 566 because the second timestamp ('12:00:00') is 566 minutes greater than the first one (02:34:12):
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'02:34:12','12:00:00')}
The following example returns 1440 because the second timestamp is one day (1440 minutes) lesser than the first one:
SELECT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,'20170406','20170405')}