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.)

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 interval-type 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 interval-type 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 non-canonical 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 (1999-12-20 12:00:00) is 7 months greater than the first one: