CODEWITHSUNDEEP
sql-servert-sqldatediff
NoWar
NoWar

Reputation: 37633

TSQL datediff issue

I just found using real data that 

datediff(SECOND, TimeStart, TimeEnd) / 60

is not the same as

datediff(MINUTE, TimeStart, TimeEnd)

I used this SELECT:

SELECT 
    DATEDIFF(SECOND, TimeStart, TimeEnd) AS seconds, 
    DATEDIFF(SECOND, TimeStart, TimeEnd) / 60 AS Mins1,
    DATEDIFF(MINUTE, TimeStart, TimeEnd) AS Mins2

and I'm getting these results:

seconds   mins1   mins2
-------------------------
  236        3       4
  620       10      10
  536        8       9
  443        7       8
  692       11      12
 1038       17      18   
  SUM       56      61

Can anyone explain it?

What is the correct approach to get minutes difference in T-SQL between two times?

Upvotes: 0

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

This is actually covered in the documentation:

Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

The issue is that users want the function to do something else. As a consequence, the number of years between 2016-12-31 23:59:59 and 2017-01-01 00:00:01 is 1 year -- even though the date/time values are 2 seconds apart.

This is one reason why experienced users of datediff() choose the smallest reasonable unit available (seconds or milliseconds) and then derive the higher units from that. Alas, this doesn't work for months, but it does work for most units.

Upvotes: 1

Related Questions