I have this query that given @secs
a number of seconds, it returns a table with year,months,weeks,days,hours,minutes and seconds.
take for example:
declare @secs int
select @secs = 19475298
SELECT FLOOR ( UpTime / 31207680 ) AS YEARS
, FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680 ) * 12 ) AS MONTHS
, FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 ) * 4.3 ) AS WEEKS
, FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 ) AS DAYS
, FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 ) AS HOURS
, FLOOR( ( UpTime / 60 ) - FLOOR( UpTime / 3600 ) * 60 ) AS MINUTES
, UpTime - FLOOR( UpTime / 60 ) * 60 AS SECONDS
FROM (
select @secs as Uptime
) AS RadheX
how can I have all these columns in a single string with 2 digits for each column?
on the example above the result would be
00:07:01:01:09:48:18
19475298
seconds into the year is actually2018-08-14 09:48:18.000
. That is a simple calculation (SELECT DATEADD(SECOND, 19475298, '20180101');
but requires context of the year the data belongs to. Without that, yeah, the method is not going to be precise at all.31207680
represents Dec 27th at 04:48 during a leap year, and December 28th at 04:48 otherwise. Like I said, this does not seem like an accurate number to use to break down a duration.date_start
to have a more accurate result