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
thanks for valuable explain. This is great !,
İt is not a bug, but I agree with you that is is not useful.
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?
gud explanation ……
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.
Hi Guys,
Could someone help me regarding the query please reply me. Thank you
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.
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,
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 ?
SELECT DATEDIFF(MONTH,’01-Jan-2014′ , ’01-Dec-2014′) AS DiifinMon the result is 11 why is there any way to calculate it accurately..