Convert Seconds to hours:minutes:seconds

[Rudi Groenewald]

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

[Madhivanan]

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

[Hugo Kornelis]

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)