Snippet Name: envseconds
Tags: helpful,scriptgeneration
Created Date: Oct 1 2021 8:05AM
Last Modified Date: Oct 1 2021 8:05AM
–desc: converts seconds to elapsed time
Dependencies: none-standalone code
default description
Sample Output:
--desc: converts seconds to elapsed time
DECLARE @seconds int = 4808
--select @seconds / 60 AS Minutes
;WITH MyCTE
AS
(
SELECT
dateadd(second,@seconds,convert(datetime,'1900-01-01')) AS ET,
convert(datetime,'1900-01-01') AS ST
)
SELECT ElapsedTime = dt.days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds
FROM
(
select [Years ] = RIGHT('000' + CONVERT(VARCHAR(128),datediff(year,0,ET-ST)-1),2),
[Months] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(month,ET-ST)-1),2),
[Days] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(day,ET-ST)-1),2),
[Hours] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Hour,ET-ST)),2),
[Minutes] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Minute,ET-ST)),2),
[Seconds] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Second,ET-ST)),2),
[Milliseconds] = RIGHT('000' + CONVERT(VARCHAR(128),datepart(millisecond,ET-ST)),2)
FROM MyCTE
) dt