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)}
Arguments
Description
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:

SQL_TSI_FRAC_SECOND

SQL_TSI_SECOND

SQL_TSI_MINUTE

SQL_TSI_HOUR

SQL_TSI_DAY

SQL_TSI_WEEK

SQL_TSI_MONTH

SQL_TSI_YEAR
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.
%TimeStamp Format
Range and Value Checking


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.
Examples
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')}
See Also
Copyright © 19972019 InterSystems Corporation, Cambridge, MA