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

declare @T intset @T = 10455836--set @T = 421151select (@T / 1000000) % 100 as hour,       (@T / 10000) % 100 as minute,       (@T / 100) % 100 as second,       (@T % 100) * 10 as millisecondselect 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)

Convert the integer into a string and then you can use the STUFF function to insert in your colons into time string. Once you've done that you can convert the string into a time datatype.

SELECT CAST(STUFF(STUFF(STUFF(cast(23421155 as varchar),3,0,':'),6,0,':'),9,0,'.') AS TIME)

That should be the simplest way to convert it to a time without doing anything to crazy.

In your example you also had an int where the leading zeros are not there. In that case you can simple do something like this:

SELECT CAST(STUFF(STUFF(STUFF(RIGHT('00000000' + CAST(421151 AS VARCHAR),8),3,0,':'),6,0,':'),9,0,'.') AS TIME)

Use the built-in MSDB.DBO.AGENT_DATETIME(20150119,0)

https://blog.sqlauthority.com/2015/03/13/sql-server-interesting-function-agent_datetime/