CODEWITHSUNDEEP
sqlsql-server
user2824374
user2824374

Reputation: 220

How to calculate DateDiff in minutes correctly?

I'm trying to find a way to calculate minutes between 2 times using DATEDIFF

SELECT DATEDIFF(minute, CAST('05:00:00' AS time), CAST('23:59:00' AS time))

This returns 1139 which is correct but when I do

SELECT DATEDIFF(minute, CAST('05:00:00' AS time), CAST('00:37:00' AS time))

I get -263.

I have different data with different end dates in my database, and my question is how can I use single query to calculate the minutes correctly for both the cases?

Upvotes: 0

Views: 156

Answers (1)

Bogner Roy
Bogner Roy

Reputation: 199

There is no issue in this case. 5 AM is after Midnight (and 37 minutes). Every day starts at 00:00:00.000 and ends up at 23:59:59.999.

Because you specified only the time, it cannot understand that you need the day after.

I suggest you to specify the date as well:

SELECT DATEDIFF(minute, CAST('2022-11-22 05:00:00' AS datetime), CAST('2022-11-23 00:37:00.000' AS datetime))

Upvotes: 3

Related Questions