How many minutes are there between 4:30:00 (four thirty) and 4:31:59 (four thirty-one and 59 seconds)?
I reckon the answer is "nearly two", but the best answer is probably the old classic "it depends" :-)
Recently I was asked to check some code to ensure something does not take longer than 1 minute, given a start time and end time down to the second. The current code was T-SQL's DATEDIFF function, and when input the my example above, the answer was "1 minute":
SELECT
DATEDIFF(mi, '1/Jan/2000 4:30:00', '1/Jan/2000 4:31:59')
Clearly, 1 minute and 59 seconds is longer than 1 minute. What gives?
It turns out that the DATEDIFF definition (from BOL) is "Returns the number of date and time boundaries crossed between two specified dates". This means that 119 seconds has only crossed 1 minute boundary, so 1 is returned.
This actually makes sense to me (!), though I had to dig a bit deeper to find that for the purposes of my investigation, seconds were ignored anyway during the comparison. So while technically the time taken is more than a minute, only 1 minute has passed (not 2) according to boundaries.
Tags: sql server, t-sql, datediff, function