SQL DATEDIFF past midnight with no date-CodePudding

Is there any way to calculate the diff between two times without a full date time and not get the wrong answer if the time goes passed midnight? The line below could go from 23:00 - 03:20 which should be 4 hours and 20 minutes. but when I use DATEDIFF without dates in front of the times it always gives me 19 hours and 40 minutes. I've tried to change the order of the time and that doesn't help. I really don't have an option for dates in this one. Any help would be greatly appreciated.

DATEDIFF(MINUTE, MT.MilTime , MT1.MilTime) AS TotalRun,

CodePudding user response：

Tyr this

DECLARE @Date1 time = '23:00:00.000';
DECLARE @Date2 time = '03:20:00.000';

SELECT DATEDIFF(MINUTE,@Date1,@Date2),CONVERT(varchar(5), 
       DATEADD(minute, DATEDIFF(minute, @Date1, @Date2), 0), 114)

CodePudding user response：

If you're after a formatted answer (e.g., 04:20 in your example) then @JohnConner's answer is a good one - which is why I've also given it an upvote.

However, if you want to get the number of minutes between the two (as an integer) you can do an initial check (which one is greater) then perform different calculations.

Alternatively, there is an easier way

Take the datediff as for @JohnConner's answer DATEDIFF(MINUTE,@Date1,@Date2)
Add 1440 to the answer
Take the 'mod 1440' of the result (e.g., divide by 1440, and only take the remainder)

It works because 1440 is the number of minutes in a day,

If the initial result is negative (going past midnight) it adds a day's worth of minutes. The new result will be a) the correct answer, and b) less than 1440, so the mod won't do anything.
If the initial result is positive (e.g., @Date2 is after @Date1) then adding 1440, and doing a mod 1440, will return the result to the same thing it was initially.

DECLARE @Date1 time = '23:00:00.000';
DECLARE @Date2 time = '03:20:00.000';

SELECT (DATEDIFF(MINUTE,@Date1,@Date2)   1440) % 1440 AS Mins_Difference

Note that any other coder reading it won't know what you're doing, so it's a good idea to ensure you have a comment saying what the calculation does.