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 (http://blog.SQLAuthority.com)

31 thoughts on “SQL SERVER – DATEDIFF – Accuracy of Various Dateparts

  1. 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.

    Like

  2. 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

    Like

  3. 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.

    Like

  4. 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.

    Like

  5. 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.

    Like

  6. 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

    Like

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

    Like

  8. 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

    Like

  9. 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?

    Like

  10. 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

    Like

  11. 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

    Like

  12. 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

    Like

  13. 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

    Like

  14. can you do something like this?
    select datediff(minute, select date from xyz where A=4232, getdate()) as minutediff
    I want to use date from database with where clause. is it possible?

    Like

  15. Hi Guys,
    I need a help. I am new to SQL.
    i need to find difference for uniqueID in Hours between two dates(where most of the time two dates can be the same date or different),
    and am using DATEDIFF function.

    its pulling out data but not what i am looking for, I would like to have the the difference for uniqueID which is “opened recently” and” time of contact” need to fin difference for recent activities for uniqueID where its showing all the deatils for uniquieID which was created in past.
    I have tried usinf DISTICNT FUNTION as well but its showing all the data for the particular uniqueID rather than only one.

    I know its bit confusing but please can some help me regarding this.
    how to get exact difference between either two different dates or same date in hours for the most recent details for uniqueID.

    Like

  16. where complaint_complaintdate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, -1)
    AND complaint_complaintdate <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), -1)

    Hi Pinal,
    the above code which will show the result of the current week time internal but i didn't understand how its work so i need clear explanation for e.g. datediff(day, 0, getdate()) which give some integer value that provide the result 41550 but how it will calculate with 41550 /7*7
    so can you give me clear explanation for each parameter value i.e. how dateadd works and how datediff works and how it will calculate with the DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, -1)

    so i will waiting for postive response.

    Like

  17. I’m having difficulties resolving errors with the following select DATEDIFF (this is only the first part – but, I think I’ll be able to manage the rest if I can figure out where the error lies):

    SELECT
    — PATIENT.PAT_MRN_ID,
    — ED_IEV_PAT_INFO.PAT_CSN,
    — PATIENT.PAT_NAME,
    ED_IEV_EVENT_INFO.EVENT_DISPLAY_NAME,
    ED_IEV_EVENT_INFO.EVENT_TIME ED_ARRIVED_TIME
    –ROW_TABLE_TRIAGE.EVENT_DISPLAY_NAME,
    — ROW_TABLE_TRIAGE.EVENT_TIME,
    FROM ED_IEV_EVENT_INFO
    DATEDIFF (MINUTE,ED_IEV_EVENT_INFO.EVENT_TIME,ROW_TABLE_TRIAGE.EVENT_TIME)ARRIVE_TRIAGE_min,
    CAST (DATEDIFF (MINUTE,ED_IEV_EVENT_INFO.EVENT_TIME,ROW_TABLE_TRIAGE.EVENT_TIME)AS float(2))/60 ARRIVE_TRIAGE_hr,
    (CASE
    WHEN (DATEDIFF (MINUTE,ED_IEV_EVENT_INFO.EVENT_TIME,ROW_TABLE_TRIAGE.EVENT_TIME)) = 360
    THEN ‘> 6′
    END) CATEGORY_ARRIVAL_TRIAGE,

    Like

  18. Recently I tried below queries in SQL

    SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, ‘2014-05-10 23:59:59.999′))
    SELECT DATEADD(DD, 0, DATEDIFF(DD, 0, ‘2014-05-10 23:59:59.998′))

    It gives different dates respectively as below,

    2014-05-11 00:00:00.000
    2014-05-10 00:00:00.000

    Further breaking down of sub queries gave me different results as shown in below snippets.

    select DATEDIFF(DD, 0, ‘2014-05-10 23:59:59.999′) as [DATEDIFF]
    select DATEDIFF(DD, 0, ‘2014-05-10 23:59:59.998′) as [DATEDIFF]

    I feel ideally the output for both the queries should have been same but I got different output for both the queries respectively as shown below

    41768
    41767

    Can anybody explain why this is so ?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s