I recently received the following question through email and I found it very interesting so I want to share it with you.
“Hi Pinal,
In SQL statement below the time difference between two given dates is 3 sec, but when checked in terms of Min it says 1 Min (whereas the actual min is 0.05Min)
SELECT DATEDIFF(MI,'2011-10-14 02:18:58' , '2011-10-14 02:19:01') AS MIN_DIFF
Is this is a BUG in SQL Server ?”
Answer is NO.
It is not a bug; it is a feature that works like that. Let us understand that in a bit more detail. When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1.
The following will also clear how DATEDIFF works:
SELECT DATEDIFF(YEAR,'2011-12-31 23:59:59' , '2012-01-01 00:00:00') AS YEAR_DIFF
The difference between the above dates is just 1 second, but in terms of year difference it shows 1.
If you want to have accuracy in seconds, you need to use a different approach. In the first example, the accurate method is to find the number of seconds first and then divide it by 60 to convert it to minutes.
SELECT DATEDIFF(second,'2011-10-14 02:18:58' , '2011-10-14 02:19:01')/60.0 AS MIN_DIFF
Even though the concept is very simple it is always a good idea to refresh it. Please share your related experience with me through your comments.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
These types of posts make me miss Oracle’s TO_DATE and TO_CHAR functions. I was just working through this the other day with hours, I wanted to check the last backup of a database, and find all that had been longer than one hour… but I wanted something like 1:05AM and 2:00AM to be included in the hour interval. It took me a few minutes longer than it should have to code it up.
Awesome read! It was a really good… Never knew this thing about datediff.
Thanks for such informative post.
Interesting and good one.
Really Nice Explanation ….
Thanks for sharing.
I just want to comment on below section
“When you instruct SQL Server to find the time difference in minutes, it just looks at the minute section only and completely ignores hour, second, millisecond, etc. So in terms of difference in minutes, it is indeed 1.”
SQL server will not exclude hours in the above case, it looks for minimum units(in this case everything above minutes will be considered like hours, days, year, etc but things that are less than minutes will not be considered like ‘seconds, milliseconds,etc’)
In the below query there is no change in minutes but i have changed hours
SELECT DATEDIFF(MI,’2011-10-14 02:18:58′ , ’2011-10-14 03:18:00′) AS MIN_DIFF
Result i got is 60
For the below query the result will be “0″ as we don’t have a minimum difference of 1 second
SELECT DATEDIFF(MI,’2011-10-14 02:18:58′ , ’2011-10-14 02:18:59′) AS MIN_DIFF
You have 03:18:00 – 02:18:58, that’s a 60 minute difference.
The result from the query is correct. It only ignores the parts lower than minutes, not hours, days and years.
You have 03:18:00 – 02:18:00, that’s a 60 minute difference.
The result from the query is correct. It only ignores the parts lower than minutes, not hours, days and years.
Though this approach won’t work if you need to find difference in months or years due to different quantity of days in different months and years. I’ve been waiting for some function which would work as it is expected since MS SQL 2000 but it hadn’t appeared.
Actually everybody can replace this function by Year(a)-Year(b) and get the result. And everybody should write quite a complex and not very fast query to calculate a REAL difference in years or months.