CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
Youbaraj Sharma
Youbaraj Sharma

Reputation: 1295

Converting Seconds into Total Hours:minutes:seconds

Can someone guide me on how to convert seconds into total hours, minutes and seconds format?

For example: 2,652,819 secs should give 736:53:39

I am able to get the same in days:hours:minutes:seconds format but I specifically require the convert the days also into total hours

Upvotes: 0

Views: 1687

Answers (3)

Nicholas Carey
Nicholas Carey

Reputation: 74385

Assuming you've got an integral value for tot_secs — floating point will require some judicious use of round(), ceil() and/or floor(), but you can try something like this:

select hours    = ( t.tot_secs / 3600 )      , -- hours   is total seconds / (secs/hr)
       minutes  = ( t.tot_secs % 3600 ) / 60 , -- minutes is whatever's left over / 60
       seconds  =   t.tot_secs % 60 , -- whatever's left over is seconds
       hh_mm_ss =                    convert(varchar,   t.tot_secs / 3600 )
                + ':' + right( '0' + convert(varchar, ( t.tot_secs % 3600 ) / 60 ) , 2 )
                + ':' + right( '0' + convert(varchar,   t.tot_secs          % 60 ) , 2 )
from ( select tot_secs = 2652819
     ) t

The above query yields

hours minutes seconds hh_mm_ss
----- ------- ------- --------
 736     53      39   736:53:39

Upvotes: 2

rageit
rageit

Reputation: 3621

This will parse seconds into days:hours:minutes:seconds.

declare @seconds int = 2652819 
select @seconds / (60 * 60 * 24) [days], (@seconds / (60 * 60)) % 60 [hours], (@seconds / 60) % 60 [minutes], @seconds % 60 seconds
select cast(@seconds / (60 * 60 * 24) as varchar(10)) + ':' + 
       cast((@seconds / (60 * 60)) % 60 as varchar(2))  + ':' + 
       cast((@seconds / 60) % 60 as varchar(2)) + ':' +
       cast(@seconds % 60 as varchar(2)) as [days:hours:minutes:seconds]

Output:

days|hours|minutes|seconds
----|-----|-------|-------
30  | 16  |  53   |  39

days:hours:minutes:seconds
--------------------------
30:16:53:39

Upvotes: 0

Lamak
Lamak

Reputation: 70678

This works:

DECLARE @Seconds INT
SET @Seconds = 2652819 

SELECT  CAST(@Seconds/3600 AS VARCHAR(10)) + ':' +
        CAST((@Seconds%3600)/60 AS VARCHAR(10)) + ':' + 
        CAST(@Seconds%60 AS VARCHAR(2))

Upvotes: 0

Related Questions