Today someone requested a native sql way to convert a time duration into a friendly “00hh 15mm 20ss” format.
There are lots of ways to tackle this, but one of them is below.
This is a function that will take a # of seconds and format it according to your wishes.
There are a few options, which I hope are self-explanatory.
Once you’ve created the function, you can test it with a script like this:
/* Suppose something that started 1 hour and 45 minutes ago, and finished 30 seconds ago We'd like something that looks pretty like "1 hour, 44 minutes, and 30 seconds" */ declare @startdate datetime = dateadd(minute,-105,getdate()) ,@enddate datetime = dateadd(second,-30,getdate()) ; declare @timedifferenceinseconds int = datediff(second,@startdate,@enddate) select 'That job started ' + dbo.usr_ufn_formattedduration(@timedifferenceinseconds,default,' hour',' minute',' second',', ',1,1) + ' ago.' as message
Which will give you output like this:
And here is the code for the actual UFN.
create function usr_ufn_formattedduration( @durationseconds int ,@daytag nvarchar(100) = 'day' ,@hourtag nvarchar(100) = 'hh' ,@minutetag nvarchar(100) = 'mm' ,@secondtag nvarchar(100) = 'ss' ,@separator nvarchar(100) = ' ' ,@usedays bit = 0 ,@pluralize bit = 0 ) returns nvarchar(100) as begin if 1 = @usedays begin --sql will always round down, so 5 days and 23 hours becomes just "5" declare @days int = (@durationseconds / 86400); --those days used up a bunch of seconds. take them out before proceeding. set @durationseconds = @durationseconds - (@days * 86400); end; --repeat for hours and minutes - same process here, except we know --there are only 3600 secs in an hour, 60 seconds in a minute --you could generalize those constant numbers, if you expect to --use this in another solar system. declare @hours int = (@durationseconds / 3600); set @durationseconds = @durationseconds - (@hours * 3600); declare @minutes int = @durationseconds / 60; set @durationseconds = @durationseconds - (@minutes * 60); --anything left over at this point is just a second declare @seconds int = @durationseconds; --if requested, add an "s" to the end of tags if their value is >1. --we'll just assume "s" is appropriate. If you want to identify hours as "sheep", --then you have problems, and you will just have to deal with "sheeps". if 1 = @pluralize begin select @daytag = case when 1 < @days then @daytag + 's' else @daytag end ,@hourtag = case when 1 < @hours then @hourtag + 's' else @hourtag end ,@minutetag = case when 1 < @minutes then @minutetag + 's' else @minutetag end ,@secondtag = case when 1 < @seconds then @secondtag + 's' else @secondtag end end; --convert to strings for friendly output declare @daystring nvarchar(100) = ''; if 1 = @usedays begin set @daystring = convert(nvarchar(100),@days) + @daytag + @separator; end; declare @hourstring nvarchar(100) = convert(nvarchar(100),@hours) + @hourtag; declare @minutestring nvarchar(100) = convert(nvarchar(100),@minutes) + @minutetag; declare @secondstring nvarchar(100) = convert(nvarchar(100),@seconds) + @secondtag; return @daystring + @hourstring + @separator + @minutestring + @separator + @secondstring; end;