Calculate time difference in minutes in SQL Server

Question

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.

Answer 1

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 

Answer 2

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

Answer 3

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