SQL time difference between two dates result in hh:mm:ss

Question

I am facing some difficulty with calculating the time difference between two dates.

What I want is, I have two dates let say

@StartDate = '10/01/2012 08:40:18.000'
@EndDate='10/04/2012 09:52:48.000'

so the difference between two dates in the form of hh:mm:ss is 72:42:30.

How can I get this result in a T-SQL query?

Answer 1

declare @StartDate datetime, @EndDate datetime

select @StartDate = '10/01/2012 08:40:18.000',@EndDate='10/04/2012 09:52:48.000'

select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]

This query will helpful to you.