Caché SQL Reference
[Back] [Next]
Go to:

A scalar date/time function that returns an integer count of the difference between two timestamps for a specified date part.
{fn TIMESTAMPDIFF(interval-type,startdate,enddate)}
interval-type The type of time/date interval that the returned value will represent.
startdate A TIMESTAMP value expression.
enddate A TIMESTAMP value expression that will be compared to startdate.
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 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.
TIMESTAMPDIFF and DATEDIFF do not handle quarters (3-month intervals).
The timestamp argument values have the same logical format and external format: a string of the form 'yyyy-mm-dd hh:mm:ss'.
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.
Range and Value Checking
TIMESTAMPDIFF performs the following checks on input values.
The following example returns 7 because the second timestamp (1999-12-20 12:00:00) is 7 months greater than the first one:
     '1999-5-19 00:00:00','1999-12-20 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,'2017-04-06','2017-04-05')}
See Also