How to convert an integer (time) to HH:MM:SS::00 in SQL Server 2008?

Question

Here I have a table with a time column (datatype is integer), now I need to convert the integer value to time format HH:MM:SS:00 in SQL Server 2008.

Also need clarification in the above time format, whether 00 represents milliseconds?

Please help on this.

example: 23421155 represents 23:42:11:55; 421151 represents 00:42:11:51

Hope that it is clear now.

Answer 1

declare @T int

set @T = 10455836
--set @T = 421151

select (@T / 1000000) % 100 as hour,
       (@T / 10000) % 100 as minute,
       (@T / 100) % 100 as second,
       (@T % 100) * 10 as millisecond

select dateadd(hour, (@T / 1000000) % 100,
       dateadd(minute, (@T / 10000) % 100,
       dateadd(second, (@T / 100) % 100,
       dateadd(millisecond, (@T % 100) * 10, cast('00:00:00' as time(2))))))  

Result:

hour        minute      second      millisecond
----------- ----------- ----------- -----------
10          45          58          360

(1 row(s) affected)


----------------
10:45:58.36

(1 row(s) affected)