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

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)

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/

This will work:

DECLARE @MS INT = 235216
select cast(dateadd(ms, @MS, '00:00:00') AS TIME(3))

(where ms is just a number of seconds not a timeformat)

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

Related

Recent Posts