A scalar date/time function that returns the integer number of intervals of a specified type between two timestamps.
Synopsis
{fn TIMESTAMPDIFF(interval-type,startdate,enddate)}
Arguments
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.
Description
The TIMESTAMPDIFF function returns the difference between two given timestamps (that is, one timestamp is subtracted from the other) for the specified interval (seconds, days, weeks, etc.). The value returned is an INTEGER that specifies the number of these intervals between the two timestamps. (If enddate is earlier than startdate, TIMESTAMPDIFF returns a negative INTEGER value.) You can return an interval of fractional seconds, counted in thousandths of a second (.001).
The interval-type parameter can be one of the following timestamp intervals:
TIMESTAMPDIFF and DATEDIFF do not handle quarters (3-month intervals).
The timestamp parameter 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. If a value fails a check, the null string is returned.
Examples
The following example returns 7 because the second timestamp (1999-12-20 12:00:00) is 7 months greater than the first one:
SELECT DISTINCT {fn TIMESTAMPDIFF(SQL_TSI_MONTH,
     '1999-5-19 00:00:00','1999-12-20 12:00:00')}
FROM Sample.Person
 
The following example returns 566 because the second timestamp ('12:00:00') is 566 minutes greater than the first one (02:34:12):
SELECT DISTINCT {fn TIMESTAMPDIFF(SQL_TSI_MINUTE,
     '02:34:12','12:00:00')}
FROM Sample.Person
 
See Also
TIMESTAMPADD DATEDIFF