Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Formatting Timespans - SQL Server

RSS
Modified on Thu, Aug 04, 2016, 7:48 AM by Administrator Categorized as 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

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.