Jeff Smith wrote back in 2007 a great article "Working with Time Spans and Durations in SQL Server", but I wanted to avoid the strange date-like part and the multiple columns he worked with.
To combine the different parts into one single-column answer I use part indicators like 'd' for days, 'h' for hours, 'm' for minutes and 's' for seconds. That I think is quite common indicators and should be immediately recognizable to most people. Also non-IT end users.
Most log solutions give a duration with a lot of seconds, like a SQL Server database backup. But that can be difficult to comprehend and compare directly
DECLARE @duration INT = 221231; -- seconds
SELECT CAST(@duration / 86400 AS varchar(3)) + 'd' + CAST((@duration % 86400) / 3600 AS varchar(2)) + 'h' + CAST((@duration % 3600) / 60 AS varchar(2)) + 'm' + CAST(@duration % 60 AS varchar(2)) + 's'
This gives the output
That is 2 days, 13 hours, 27 minutes and 11 seconds.
The motivation for this blog entry is database backup history, and the formatting above can be used against the history
,backup_set_id, media_set_id, name
,CAST(DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) / 86400 AS varchar(3)) + 'd '
+ CAST((DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 86400) / 3600 AS varchar(2)) + 'h '
+ CAST((DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 3600) / 60 AS varchar(2)) + 'm '
+ CAST(DATEDIFF(SECOND, [backup_start_date], [backup_finish_date]) % 60 AS varchar(2)) + 's' AS [duration_formatted]
,(backup_size / 1024 / 1024 / 1024) as [backup_size_gb]
,(compressed_backup_size / 1024 / 1024 / 1024) as [compressed_backup_size_gb]
--WHERE [database_name] = 'my_large_database'
ORDER BY [backup_finish_date] DESC
You can comment in or out lines in the statement above to get what you need in the given situation.
Maybe one can use STUFF to insert values in pre-formatted output, but that I could not get to work in first shot. And it did not really add significant value to the output...