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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 11728

# 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...

Thanks
0
Robb Hill
4 Solutions

Billing EngineerCommented:
like this, for example:

select convert(varchar(10), dateadd(second, <number of seconds>, 0) , 8)
0

Commented:

Will give you HH:MM:SS result. Converting to varchar(5) will drop off the seconds.
0

Senior .Net DeveloperAuthor Commented:
what is the difference between teh two answers....here...i ran it..and am getting the same results for both formulas
0

Commented:
select convert(varchar(10), dateadd(second, <number of seconds>, 0) , 8)
Gives you the format hh:mi:ss

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.
0

Senior 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.
0

## Featured Post

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