How to display date difference in Days:Hours:Minutes format using TSQL

By Allen Stoner

Often times on reports from Microsoft SQL server it is nice to display the difference between two datetime fields in something other than just the number of minutes, or days. This code snippet shows how to format the datediff output for minutes into hours, days, minutes format. It could easily be placed in a user defined function as well as have logic added to it for seconds and milliseconds if necessary.

declare @StartDate as datetime = '2/4/11 7:20 am'
declare @EndDate as datetime = '2/6/12 9:25 pm'

declare @Diff as bigint
declare @Line as varchar(20)

select @Diff = DATEDIFF(N, @StartDate, @EndDate)
select @Line = ltrim(str(@Diff / 1440)) + ':'
select @Diff = @Diff - (FLOOR(@Diff / 1440) * 1440)
select @Line = @Line + REPLICATE('0', 2 - len(ltrim(str(@Diff / 60)))) + ltrim(str(@Diff / 60)) + ':'
select @Diff = @Diff - (FLOOR(@Diff / 60) * 60)
select @Line = @Line + REPLICATE('0', 2 - len(ltrim(str(@Diff)))) + ltrim(str(@Diff))

Print @Line

How to display date difference in Days:Hours:Minutes format using TSQL  (2233 Views)