Using MSSQL to output time in days, hours and minutes

I was working on some code today where I wanted to output a time span in the format 1d 4h 36m (i.e. "1 day, 4 hours and 36 minutes.") I wanted a pure SQL solution so that I didn't have to worry about formatting later. Here's some example SQL that will format a time span by days, hours and minutes.

declare @startTime datetime
set @startTime = '2009-07-11 14:19:40.000'

declare @endTime datetime
set @endTime = getUtcDate()

select
    convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd '
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
  + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm' as Format1
  , case
      when (((dateDiff(mi, @startTime, @endTime)/(24*60))) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + 'd '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
      when (((dateDiff(mi, @startTime, @endTime)%(24*60)/60)) > 0) then
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60) + 'h '
        + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
      else
          convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60) + 'm'
    end as Format2
  , convert(varchar(40), dateDiff(mi, @startTime, @endTime)/(24*60)) + ':'
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%(24*60)/60), 2) + ':'
  + right('00' + convert(varchar(40), dateDiff(mi, @startTime, @endTime)%60), 2) as Format3

NOTE:

Make sure to keep the dateDiff() time in minutes. If you switch to hours (or days) you'll have rounding issues you'll have to work around. By sticking to using minutes for the equations, you avoid the rounding issues.

When you run this query in Microsoft SQL Server, you'll see that it outputs 3 columns—Format1, Format2 and Format3.

Format1 always outputs the time span in format 0d 0h 0m, even when the days or hours are 0. In my case, I really wanted to ignore days if no days have passed and hours if we're still under 60 minutes. This lead me to create Format2.

Format2 still uses the 0d 0h 0m format, but it will drop off days if less than 24 hours old and drop hours if less than 60 minutes has passed. This leaves you strings like "6d 4h 52m", "4h 10m" or "3m". In my case, this was the best formatting for my uses.

Format3 is an alternative format that places the time span in the format d:hh:mm. While I'm not using that formatting currently, some people may find it useful. This also shows off how you can force the hours and minutes to output as a digit value (i.e. 0:02:01 = 0 days, 2 hours and 1 minute.)

Hopefully this will prove useful to some of you.