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

6
  • Is 31207680 supposed to represent the number of seconds in a year? That's close, but not precise. Oct 29, 2018 at 20:00
  • what could be a better option? there is the number of days in february issue too Oct 29, 2018 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. Oct 29, 2018 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. Oct 29, 2018 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 Oct 29, 2018 at 20:13

1 Answer 1

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
6
  • 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/… Oct 29, 2018 at 20:09
  • why varchar if they will always be 2 chars? -- just asking Oct 29, 2018 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'. Oct 29, 2018 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, 2018 at 20:28
  • 1
    Yes you need at least 3 (and you can use '0' on the left, you don't need '00'). Oct 30, 2018 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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