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