# 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 Time11:15:00 | 13:15:0010:45:00 | 18:59:00``

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

Anybody please help out. Thanks in advance.

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 statements, 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 statement 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 statement doesn't need to be repeated, then rolling these 24 numbers back up into columns using `PIVOT`

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
``````

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..