By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,274 Members | 2,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,274 IT Pros & Developers. It's quick & easy.

Convert Seconds to hours:minutes:seconds

P: n/a
Hi all.

If I've got a query which has a field with seconds in it... how will I use
the Convert function to get my field converted to the format: HH:MM:SS ?

The field with the seconds in is called: "Diff"
Thanks alot

Rudi

Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a

I am not sure where this is correct way of finding

Try this

declare @sec int
set @sec=7612
select
convert(varchar(5),@sec/3600)+':'+convert(varchar(5),@sec%3600/60)+':'+convert(varchar(5),(@sec%60))

Madhivanan

Jul 23 '05 #2

P: n/a
On Wed, 9 Mar 2005 11:06:07 +0200, Rudi Groenewald wrote:
If I've got a query which has a field with seconds in it... how will I use
the Convert function to get my field converted to the format: HH:MM:SS ?

The field with the seconds in is called: "Diff"


Hi Rudi,

You can use the suggestion Madhivanan proposes (that one will display
total hours, even if it's more than 24, but it will trim seconds and
minutes, leading to output like 11:5:3 instead of 11:05:03).

An other simple way (that will only work correct if the number of hours
is less than 24) is to use datetime logic:

SELECT CONVERT(char(8), DATEADD(second, Diff, '0:00:00'), 108)

If you need the ability to handle times > 24 hours, AND you want to
display 11:05:03 instead of 11:5:3, then you need to use a slightly more
complicated version of Madhivanan's suggestion:

SELECT CONVERT(varchar(6), Diff/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (Diff % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), Diff % 60), 2)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.