Search code examples
sqldatediff

SQL DATEDIFF past midnight with no date

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,

Solution

  • Try below

    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);