[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11728
  • Last Modified:

SQL SERVER + convert seconds to hours and minutes

How do you within a query using sql 2000 convert a number in seconds....into hours and minutes...

Robb Hill
Robb Hill
4 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
like this, for example:

select convert(varchar(10), dateadd(second, <number of seconds>, 0) , 8)
Select Convert(varchar(8),Dateadd(s,NumberOfSeconds,0),14) From MyTable

Will give you HH:MM:SS result. Converting to varchar(5) will drop off the seconds.
Robb HillSenior .Net DeveloperAuthor Commented:
what is the difference between teh two answers....here...i ran it..and am getting the same results for both formulas
select convert(varchar(10), dateadd(second, <number of seconds>, 0) , 8)  
Gives you the format hh:mi:ss

Select Convert(varchar(8),Dateadd(s,NumberOfSeconds,0),14) From MyTable
Gives you the format hh:mi:ss:mmm(24h)  but it's truncating off the milliseconds at the end because you only converted to varchar(8).  If you converted to varchar(11) you'd see the milliseconds.
Scott PletcherSenior DBACommented:
Those methods will work as long as you *always have less than 24 hours*.  If you could have more than 24 hours, you need to do something like this:

DECLARE @NumberOfSeconds INT
SET @NumberOfSeconds = (25 * 60 * 60) /*hrs*/ + (7 * 60) /*mins*/ + 57 /*left-over secs*/
SELECT CAST(@NumberOfSeconds / 3600 AS VARCHAR(3)) + ':' +
    RIGHT('0' + CAST(@NumberOfSeconds % 3600 / 60 AS VARCHAR(2)), 2)

If you need to round, replace @NumberOfSeconds with (@NumberOfSeconds + 30) in the calcs.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now