Show average difference between two time fields as MM:SS in SQL

I am using SQL Server 2008. I have several rows of start_time and end_time. I want to calculate the average difference between these two times in a MM:SS format.

start_time | end_time     
10:15:30   | 10:15:45     
10:45:00   | 10:47:30

Row 1 would be a difference of 00:15, and row 2 would be a difference of 02:30. The average of the entire dataset would be 01:23 (1 minute and 23 seconds).

The code I’m using looks like the following, but only returns an integer.

AVG(DATEDIFF(MI,start_time,end_time))

Thanks in advance for your help.

Answer

You’re close, but you should use DateDiff() to get the average number of seconds between the two fields, rather than the average number of minutes.

With SQL Server 2008 R2, you don’t have access to TIMEFROMPARTS() which would simplify the display greatly, so you’ll have to convert this into a VARCHAR to get the format you want.

;With AverageSeconds As
(
    Select  Avg(DateDiff(Second, Start_Time, End_Time))) AvgSec
    From    YourTable
)
Select  Right('00' + Convert(Varchar, (AvgSec / 60)), 2) 
             + ':' 
      + Right('00' + Convert(Varchar, (AvgSec % 60)), 2)
From    AverageSeconds