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 (https://blog.sqlauthority.com)
32 Comments. Leave new
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.
want to get hh:mm format
i want minutes difference between 2012-01-02 12:55:55.568 and 2012-01-03 10:12:56.698
Difference in what aspect days, minutes, seconds,etc?
It was really very nice tutorial………………Thanks
Hi pinal
I need a query which display date difference in hh:mm:ss in sql.If 2 days difference then it should convert days into hours
Hi,
I have two dates say fromdate = ’13/09/2012 5:57′ and todate= ’13/09/2012 11:00′. My requirement is to get the difference like ‘0 Day 5 Hour 3 Minutes’. Can u please write a query for finding the difference like this.
Thanks,
Lijin Joy
Hi Pinal,
i have one problem in database can you solve it ? the problem is this. if a product have today price 5 rupees and after 10 days increses 5 rupees mean in 15. how we can update or refresh the price of the product in the table which is uploaded before?
Hi…
I must be missing something… can someone shed some light on this please…
DateDiff(minute, 2012-10-10 08:00:00.000, 2012-10-10 10:30:00.000)
this returns 150.
Should’nt the answer be 30… if all i am looking is the difference in minutes of the minute part.
Thanks
Joy
I think there is a confusion, if you say that the expression only looks the minute part of two dates to get the difference.
Then how the below statement gives me 721 minutes. I have changed also given AM PM suffix. If it only check minute part then it should still give us result as 1 minute.
SELECT DATEDIFF(MINUTE,’2011-10-14 02:18:58 AM’ , ‘2011-10-14 02:19:01 PM’) AS MIN_DIFF
Looks like it wrong results. Try to find out below sql statement exact difference in years, months and days. Below statement output results is 1 Year, 1 Month and 5 days. But I expected 0 years, 0 months and 5 days. Date scenario is 5th Jan 2013 to 31st Dec 2012
SELECT
DATEDIFF(Year,’2012-12-31′,’2013-01-05′) AS YearDiff,
DATEDIFF(Month,’2012-12-31′,’2013-01-05′) AS MonthDiff,
DATEDIFF(Day,’2012-12-31′,’2013-01-05′) AS DayDiff
I THINK IT IS A BUG
How to convert hours into Minuates like if time is 2:30 like this
Output will be like 2:30
hi i have also a problem with diffdate without week end, if you can help me:
i try to find days between two dates and it’s give the right result wehn the dates are in same year, but when the date change the year it’s go to wrong,
my code is:
convert (float,datediff(dd,departureDate, min(arrivalDate)) – (isnull((datepart(wk, min(arrivalDate)) – datepart(week,Event2024_actual))*2,0))) as arrivalhome
the result is