Converting Seconds as varchar into HH:MM:SS

[mbryantuk]

Hi

I have a field storing the amount of seconds taken to perform an action and it is stored as a decimal value e.g. 100.52 seconds is it possible to convert this into a datetime so i can make it display in hh:mm:ss in my view

Thanks

Matt

 

[mbryantuk]

sorry its not a varchar it is a numeric(7, 3)

 

[Otto]

Here is an example:
declare @Secs numeric(10, 2)
set @Secs=100.52

select convert(char(12), dateadd(millisecond, (@Secs-cast(@Secs as int))*100, dateadd(second, cast(@Secs as int), 0)), 114)

The result is (hh:mi:ss:mmm): 00:01:40:053

In the view, you should change the @Secs variable into your field.

 

[mbryantuk]

Thanks this worked perfectly!