Show different ways to represent the duration of a time interval starting form elapsed milliseconds

present-time-interval.sql

-- Show different ways to represent the duration of a time interval starting form elapsed milliseconds
-- adapted from  https://stackoverflow.com/a/26347259
DECLARE @x bigint = (24*60*60*1000)-2; -- milliseconds
SELECT
    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD:HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, @x, 0), 114)  AS [DD:HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD:}HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD:}HH:MM:SS.MS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD day(s) HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [DD day(s) HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD day(s)} HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD day(s)} HH:MM:SS.MS]
SET @x = @x + 4 -- just go over 1 day
SELECT
    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD:HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, @x, 0), 114)  AS [DD:HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD:}HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD:}HH:MM:SS.MS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD day(s) HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [DD day(s) HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD day(s)} HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD day(s)} HH:MM:SS.MS]
SET @x = @x + @x -- go over 2 days
SELECT
    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD:HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ':' + CONVERT(varchar, DATEADD(ms, @x, 0), 114)  AS [DD:HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD:}HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ':' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD:}HH:MM:SS.MS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [DD day(s) HH:MM:SS]
,    CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [DD day(s) HH:MM:SS.MS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, (@x % 86400) * 1000, 0), 108) AS [{DD day(s)} HH:MM:SS]
,    CASE WHEN CONVERT(varchar, @x / 1000 / 86400) > 0 THEN CONVERT(varchar, @x / 1000 / 86400) + ' day(s) ' ELSE '' END + CONVERT(varchar, DATEADD(ms, @x, 0), 114) AS [{DD day(s)} HH:MM:SS.MS]