Calculate time difference in minutes in SQL Server

I need the time difference between two times in minutes. I am having the start time and end time as shown below:

start time | End Time 11:15:00 | 13:15:00 10:45:00 | 18:59:00 

I need the output for first row as 45,60,15 which corresponds to the time difference between 11:15 and 12:00, 12:00 and 13:00, 13:00 and 13:15 respectively.

6 Answers

Use DateDiff with MINUTE difference:

SELECT DATEDIFF(MINUTE, '11:10:10' , '11:20:00') AS MinuteDiff 

Query that may help you:

SELECT StartTime, EndTime, DATEDIFF(MINUTE, StartTime , EndTime) AS MinuteDiff FROM TableName 

The following works as expected:

SELECT Diff = CASE DATEDIFF(HOUR, StartTime, EndTime) WHEN 0 THEN CAST(DATEDIFF(MINUTE, StartTime, EndTime) AS VARCHAR(10)) ELSE CAST(60 - DATEPART(MINUTE, StartTime) AS VARCHAR(10)) + REPLICATE(',60', DATEDIFF(HOUR, StartTime, EndTime) - 1) + + ',' + CAST(DATEPART(MINUTE, EndTime) AS VARCHAR(10)) END FROM (VALUES (CAST('11:15' AS TIME), CAST('13:15' AS TIME)), (CAST('10:45' AS TIME), CAST('18:59' AS TIME)), (CAST('10:45' AS TIME), CAST('11:59' AS TIME)) ) t (StartTime, EndTime); 

To get 24 columns, you could use 24 case expressions, something like:

SELECT [0] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime) ELSE 60 - DATEPART(MINUTE, StartTime) END, [1] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 1 THEN DATEPART(MINUTE, EndTime) WHEN DATEDIFF(HOUR, StartTime, EndTime) > 1 THEN 60 END, [2] = CASE WHEN DATEDIFF(HOUR, StartTime, EndTime) = 2 THEN DATEPART(MINUTE, EndTime) WHEN DATEDIFF(HOUR, StartTime, EndTime) > 2 THEN 60 END -- ETC FROM (VALUES (CAST('11:15' AS TIME), CAST('13:15' AS TIME)), (CAST('10:45' AS TIME), CAST('18:59' AS TIME)), (CAST('10:45' AS TIME), CAST('11:59' AS TIME)) ) t (StartTime, EndTime); 

The following also works, and may end up shorter than repeating the same case expression over and over:

WITH Numbers (Number) AS ( SELECT ROW_NUMBER() OVER(ORDER BY t1.N) - 1 FROM (VALUES (1), (1), (1), (1), (1), (1)) AS t1 (N) CROSS JOIN (VALUES (1), (1), (1), (1)) AS t2 (N) ), YourData AS ( SELECT StartTime, EndTime FROM (VALUES (CAST('11:15' AS TIME), CAST('13:15' AS TIME)), (CAST('09:45' AS TIME), CAST('18:59' AS TIME)), (CAST('10:45' AS TIME), CAST('11:59' AS TIME)) ) AS t (StartTime, EndTime) ), PivotData AS ( SELECT t.StartTime, t.EndTime, n.Number, MinuteDiff = CASE WHEN n.Number = 0 AND DATEDIFF(HOUR, StartTime, EndTime) = 0 THEN DATEDIFF(MINUTE, StartTime, EndTime) WHEN n.Number = 0 THEN 60 - DATEPART(MINUTE, StartTime) WHEN DATEDIFF(HOUR, t.StartTime, t.EndTime) <= n.Number THEN DATEPART(MINUTE, EndTime) ELSE 60 END FROM YourData AS t INNER JOIN Numbers AS n ON n.Number <= DATEDIFF(HOUR, StartTime, EndTime) ) SELECT * FROM PivotData AS d PIVOT ( MAX(MinuteDiff) FOR Number IN ( [0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23] ) ) AS pvt; 

It works by joining to a table of 24 numbers, so the case expression doesn't need to be repeated, then rolling these 24 numbers back up into columns using PIVOT

Apart from the DATEDIFF you can also use the TIMEDIFF function or the TIMESTAMPDIFF.

EXAMPLE

SET @date1 = '2010-10-11 12:15:35', @date2 = '2010-10-10 00:00:00'; SELECT TIMEDIFF(@date1, @date2) AS 'TIMEDIFF', TIMESTAMPDIFF(hour, @date1, @date2) AS 'Hours', TIMESTAMPDIFF(minute, @date1, @date2) AS 'Minutes', TIMESTAMPDIFF(second, @date1, @date2) AS 'Seconds'; 

RESULTS

TIMEDIFF : 36:15:35 Hours : -36 Minutes : -2175 Seconds : -130535 

You can use DATEDIFF(it is a built-in function) and % (for scale calculation) and CONCAT for make result to only one column

select CONCAT('Month: ',MonthDiff,' Days: ' , DayDiff,' Minutes: ',MinuteDiff,' Seconds: ',SecondDiff) as T from (SELECT DATEDIFF(MONTH, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 12 as MonthDiff, DATEDIFF(DAY, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 30 as DayDiff, DATEDIFF(HOUR, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 24 as HourDiff, DATEDIFF(MINUTE, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS MinuteDiff, DATEDIFF(SECOND, '2017-10-15 19:39:47' , '2017-12-31 23:59:59') % 60 AS SecondDiff) tbl 

Please try as below to get the time difference in hh:mm:ss format

Select StartTime, EndTime, CAST((EndTime - StartTime) as time(0)) 'TotalTime' from [TableName]

Try this..

select starttime,endtime, case when DATEDIFF(minute,starttime,endtime) < 60 then DATEDIFF(minute,starttime,endtime) when DATEDIFF(minute,starttime,endtime) >= 60 then '60,'+ cast( (cast(DATEDIFF(minute,starttime,endtime) as int )-60) as nvarchar(50) ) end from TestTable123416 

All You need is DateDiff..