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

SQL SERVER - DATEDIFF - Accuracy of Various Dateparts mindiff

Solarwinds

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

SQL SERVER - DATEDIFF - Accuracy of Various Dateparts mindiff1

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

SQL SERVER - DATEDIFF - Accuracy of Various Dateparts mindiff2

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)

Solarwinds
,
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

  • 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

    Reply
  • thanks for valuable explain. This is great !,
    İt is not a bug, but I agree with you that is is not useful.

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

    Reply
  • gud explanation ……

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

    Reply
  • Hi Guys,
    Could someone help me regarding the query please reply me. Thank you

    Reply
  • Anthony Stalin
    October 5, 2013 12:07 pm

    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.

    Reply
  • Dorothy Williams
    January 7, 2014 8:20 pm

    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,

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

    Reply
  • SELECT DATEDIFF(MONTH,’01-Jan-2014′ , ’01-Dec-2014′) AS DiifinMon the result is 11 why is there any way to calculate it accurately..

    Reply

Leave a Reply

Menu