Formatting Timespans - SQL Server

Overview¶

This article provides a formula to format a time span in a human-readable format.

Problem Statement¶

Given: A table with StartedOn and EndedOn datetime columns, present the elapsed times in terms of days, hours, minutes, and seconds.

Reusable Code¶

declare 
     @baseDate  datetime    = '2016-01-01'

select
     RunTime        = convert(varchar(10), datediff(minute, StartedOn, coalesce(EndedOn, getutcdate())) / 1440 ) + 'd, ' +
                        convert(varchar(30), dateadd(second, datediff(SECOND, StartedOn, coalesce(EndedOn, getutcdate())), @basedate), 8)

from 
    dbo.MyTable

Sample Output¶

1d, 22:06:33