Музыка: | Reverend Glasseye And His Wooden Legs - Black River Falls |
Entry tags: | code, sql |
[programmism] DateDiff
You would think that the T-SQL function that is called DateDiff would return the difference between the two dates. For example, if your two dates are 10:53 and 10:54 (the difference is 1 minute), and you want to get the difference in hours, you would expect that 1 minute difference would be rounded to 0 hours. It does happen with 10:53 and 10:54, however, you'll be surprised when you calculate the difference between 10:59 and 11:00. A quick check shows that the difference between '1/31/2005 23:59:59' and '2/1/2005' (1 second time interval) is equal to 1 minute, 1 hour, 1 day and 1 month depending on the first argument to DateDiff!
SELECT DateDiff(ss, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')
SELECT DateDiff(ss, '1/31/2005 23:59:59', '2/1/2005')
SELECT DateDiff(mi, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')
SELECT DateDiff(mi, '1/31/2005 23:59:59', '2/1/2005')
SELECT DateDiff(hh, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')
SELECT DateDiff(hh, '1/31/2005 23:59:59', '2/1/2005')
SELECT DateDiff(dd, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')
SELECT DateDiff(dd, '1/31/2005 23:59:59', '2/1/2005')
SELECT DateDiff(mm, '1/1/2005 1:00:00 AM', '1/1/2005 1:00:01 AM')
SELECT DateDiff(mm, '1/31/2005 23:59:59', '2/1/2005')
Of course, it is mentioned in the help - "DATEDIFF Returns the number of date and time boundaries crossed between two specified dates", but who in their right mind decided to implement it like that??? Note to self: only use DateDiff for seconds (ss), as others are not helpful at all..