469,110 Members | 2,009 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,110 developers. It's quick & easy.

Convert Seconds to hours:minutes:seconds

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
2 80148

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

Similar topics

3 posts views Thread by fred14214 | last post: by
7 posts views Thread by tshad | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.