seconds in hour:minute:second format 1

[557]

can anyone tell me how i can convert a number which is supposed to be a duration in seconds to look like hour:minute:second format

 

[hmckillop]

not exactly the tidiest way but nonetheless a way that works.

declare @x int
set @x = 100019 --you want to return 00010
select @x/3600 as hours, (@x - ((@x/3600)*3600))/60 as minutes, (@x - ((@x/3600)*3600) - ((@x - ((@x/3600)*3600))/60)*60) as seconds

"I'm living so far beyond my income that we may almost be said to be living apart

 

[donutman]

[Blue]DECLARE[/Blue] @Duration [Blue]AS[/Blue] [Blue]int[/Blue]
[Blue]SET[/Blue] @Duration[Gray]=[/Gray]12345
[Blue]SELECT[/Blue] [Fuchsia]DateAdd[/Fuchsia][Gray]([/Gray][blue]Second[/blue][Gray],[/Gray]@Duration[Gray],[/Gray]0[Gray])[/Gray]

You can add a CONVERT ( ) and SUBSTRING ( ) to get the portion of the datetime that you want. If the day is an issue then just replace the 0 with the appropriate start date.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

AFAIK format 108 performs exactly that:

select convert(varchar(8), dateadd( second, @Duration, 0 ), 108)

 

[557]

thanks everyone.

vongrunt's answer solved my problem. i was surprised at the result. i had done it in a messy way before. now, i'm going to change it to 108 format. thanks a lot vongrunt