SQL SERVER – DATEDIFF – Accuracy of Various Dateparts

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)

,
Previous Post
SQL SERVER – TRACEWRITE – Wait Type – Wait Related to Buffer and Resolution
Next Post
SQL SERVER – Dedicated Access Control for SQL Server Express Edition – An error occurred while obtaining the dedicated administrator connection (DAC) port.

Related Posts

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.

    Reply
  • Awesome read! It was a really good… Never knew this thing about datediff.
    Thanks for such informative post.

    Reply
  • Interesting and good one.

    Reply
  • Varinder Sandhu
    October 21, 2011 11:42 am

    Really Nice Explanation ….

    Thanks for sharing.

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • want to get hh:mm format

    Reply
  • i want minutes difference between 2012-01-02 12:55:55.568 and 2012-01-03 10:12:56.698

    Reply
  • Dewesh Pushkar
    May 31, 2012 9:32 am

    It was really very nice tutorial………………Thanks

    Reply
  • 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

    Reply
  • hi, pls give me the query to write for this logic, if the records exists on yesterday file but not in current files then i need to insert those records into the respective table, pls give the solution with this

    Reply
  • 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

    Reply
  • 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?

    Reply
  • 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

    Reply
  • 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

    Reply
  • 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

    Reply
  • I THINK IT IS A BUG

    Reply
  • How to convert hours into Minuates like if time is 2:30 like this

    Reply

Leave a Reply Cancel reply

Menu
Exit mobile version