The TIMESTAMPDIFF function returns an estimated number of intervals of the type defined by the first argument, based on the difference between two timestamps.
1 | Fractions of a second |
2 | Seconds |
4 | Minutes |
8 | Hours |
16 | Days |
32 | Weeks |
64 | Months |
128 | Quarters |
256 | Years |
If string-expression is a character or graphic string, it must not be a CLOB or DBCLOB.
The result of the function is an integer. If either argument can be null, the result can be null; if either argument is null, the result is the null value.
The following assumptions may be used in estimating the difference:
These assumptions are used when converting the information in the second argument, which is a timestamp duration, to the interval type specified in the first argument. The returned estimate may vary by a number of days. For example, if the number of days (interval 16) is requested for a difference in timestamps for '1997-03-01-00.00.00' and '1997-02-01-00.00.00', the result is 30. This is because the difference between the timestamps is 1 month so the assumption of 30 days in a month applies.
SELECT TIMESTAMPDIFF(64, CAST(CURRENT_TIMESTAMP-CAST(BIRTHDATE AS TIMESTAMP) AS CHAR(22))) AS AGE_IN_MONTHS FROM EMPLOYEE
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.