convert ss to hh:mm:ss in SQL

• LJB

  SSC Enthusiast

  Points: 118

  More actions

  December 16, 2003 at 9:21 am

  #101810

  I have a routine that uses datediff to return the number of seconds (ss) between the start and finish time of another routine. I would like to convert this number of seconds to the following format dd:hh:mm:ss.

  For example the 550 seconds would be 00:00:09:50 and 90,000 seconds would be 01:01:00:00.

  I think I need to use the mod division % but am unsure of the exact calculation. If anyone could point me in teh right direction I would much appriciate it.

  Thanks in advance,

  LJB

• DavidT

  SSCommitted

  Points: 1727

  More actions

  December 16, 2003 at 9:39 am

  #486433

  I was going to try and explain, to point you in the right direction, and not just post a near complete solution, however I figured the code would explain it better than I could!

  declare @sec int

  declare @s-2 varchar(2)

  declare @m varchar(2)

  declare @h varchar(2)

  declare @D varchar(10)

  set @sec = 80000

  set @s-2 = @sec%60

  set @sec = @sec/60

  set @m = @sec%60

  set @sec = @sec/60

  set @h = @sec%24

  set @sec = @sec/60

  set @D = @sec

  select

  case

  when len(@D)<2

  then replicate('0',2 - len(@D))

  else ''

  end

  + @D

  + ':'

  + replicate('0',2 - len(@H))

  + @h

  + ':'

  + replicate('0',2 - len(@M))

  + @m

  + ':'

  + replicate('0',2 - len(@S))

  + @s-2

  (Not necessarily using the smallest amount of code, just kept it clear).

  Cheers

  David

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  December 16, 2003 at 12:02 pm

  #486485

  Try something like...

  Declare @Diff DateTime

  Select @Diff = Finished - Start

  select @Diff, DateName(dy, @Diff - 1) + ':' + Convert(VarChar(10), @Diff, 14)

  
  
  Once you understand the BITs, all the pieces come together

• mccork

  SSC-Insane

  Points: 22288

  More actions

  December 16, 2003 at 7:10 pm

  #486547

  Another option:

   
  
  declare @ss int 
  
  set @ss = datediff(second, '1 Jan 2003', '31 dec 2003 23:23:59') -- for example
  
  declare @ddhhmmss varchar(20)
  
  
  
  
  
  set @ddhhmmss = 
  
      convert(varchar, datediff(day, '00:00', dateadd(second, @ss, '00:00'))) -- calculate whole days
  
      + ':' 
  
      + convert(varchar, dateadd(second, @ss, '00:00'), 108) -- calculate hh:mm:ss component
  
  
  
  select @ddhhmmss
  
  

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• LJB

  SSC Enthusiast

  Points: 118

  More actions

  December 17, 2003 at 3:03 am

  #486599

  Thanks to all,

  I've tried all three answers and they work well. I think I’ll use the answer supplied by mccork.

  Thanks again guys, I was messing around with it for a few hours and getting more and more frustrated.

  LJB