Caché SQL Reference
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

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 startdate and enddate are timestamps. These timestamps are in %Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff).
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).
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.
%TimeStamp Format
If the startdate or enddate argument is in %Library.TimeStamp data type format (yyyy-mm-dd hh:mm:ss.ffff) the following rules apply:
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