A tip was posted that shows a way to convert SQL Server's integer time value into a readable format, and does so by examining the number of digits in the integer to determine zero padding. This method works well, but does not convert time values from 00:00:01 up to 00:09:59. The related tip is posted here:
http://www.mssqltips.com/tip.asp?tip=1744
Here is an easier way to convert the integer value to a readable string with support for all time values:
SET
@time_out = right('000000' + convert(varchar, @time_in), 6)
SET
@time_out = LEFT(@time_out,2) + ':' + SUBSTRING(@time_out, 3,2) + ':' + RIGHT(@time_out, 2)
Here, the @time_out variable is a varchar(8), and @time_in is the integer time value to convert.
This method is clean and simple and covers all possible time values from midnight to midnight.
