# How to format the output of this query into year…second?

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`

• Is 31207680 supposed to represent the number of seconds in a year? That's close, but not precise. – Aaron Bertrand Oct 29 '18 at 20:00
• what could be a better option? there is the number of days in february issue too – Marcello Miorelli Oct 29 '18 at 20:02
• Well why do you only have the number of seconds to go on? By my calculation, in 2018, `19475298` seconds into the year is actually `2018-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. – Aaron Bertrand Oct 29 '18 at 20:04
• `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. – Aaron Bertrand Oct 29 '18 at 20:11
• this project is a function that shows me for how long a job has been running for. some replication jobs can go on for years. I guess I will have to add another parameter, the `date_start` to have a more accurate result – Marcello Miorelli Oct 29 '18 at 20:13

## 1 Answer

you can try Right('0'+ Cast(expression as varChar(3)), 2) like this:

``````SELECT Right('0' + Cast( FLOOR ( UpTime / 31207680 ) as varChar(3)), 2) + ':' -- YEARS
+ Right('0' + Cast( FLOOR( (UpTime / 2600640 ) - FLOOR ( UpTime / 31207680  )  * 12 )  as varChar(3)), 2) + ':' --  MONTHS
+ Right('0' + Cast( FLOOR( (UpTime / 604800 ) - FLOOR ( UpTime / 2600640 )  * 4.3 )  as varChar(3)), 2) + ':' --  WEEKS
+ Right('0' + Cast( FLOOR( (UpTime / 86400 ) - FLOOR( UpTime / 604800 ) * 7 )  as varChar(3)), 2) + ':' --  DAYS
+ Right('0' + Cast( FLOOR( ( UpTime / 3600 ) - FLOOR( UpTime / 86400 ) * 24 )  as varChar(3)), 2) + ':' --  HOURS
+ Right('0' + Cast( FLOOR( ( UpTime / 60 )   - FLOOR( UpTime / 3600 ) * 60 )  as varChar(3)), 2) + ':' --  MINUTES
+ Right('0' + Cast( UpTime - FLOOR( UpTime / 60 ) * 60  as varChar(3)), 2)  --  SECONDS
FROM (
select @secs as Uptime
) AS RadheX
``````
• Please put a length on your `varchar` or use `char(3)`. Not specifying length is a terrible example that leads to serious problems in other use cases. See sqlblog.org/2009/10/09/… – Aaron Bertrand Oct 29 '18 at 20:09
• why `varchar` if they will always be 2 `chars`? -- just asking – Marcello Miorelli Oct 29 '18 at 20:14
• They don't all start at 2 chars, that's why. If something is 10 years it will be '0' + '10' - as char(2) that becomes '01' instead of '10'. – Aaron Bertrand Oct 29 '18 at 20:21
• Now I'm confused. Sadly I don't have MS SQL here to test it, but when we concatenate '00' and '10' (varchar(2)), is it still limited at two characters? I thought it would result '0010', and then Right(..., 2) would result '10'. – Ulisses Oct 29 '18 at 20:28
• Yes you need at least 3 (and you can use '0' on the left, you don't need '00'). – Aaron Bertrand Oct 30 '18 at 12:52