0

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

this gives me: enter image description here

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

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
  • 3
    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
  • 1
    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
  • 1
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.