# [sql] 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.

This question is related to `sql` `sql-server` `sql-server-2008` `datetime` `difference`

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]

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

Use DateDiff with MINUTE difference:

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

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