How to convert an integer (time) to HH:MM:SS::00 i

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

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)

Answer 2

You can use the following time conversion within SQL like this:

--Convert Time to Integer (Minutes)
DECLARE @timeNow datetime = '14:47'
SELECT DATEDIFF(mi,CONVERT(datetime,'00:00',108), CONVERT(datetime, RIGHT(CONVERT(varchar, @timeNow, 100),7),108))

--Convert Minutes to Time
DECLARE @intTime int = (SELECT DATEDIFF(mi,CONVERT(datetime,'00:00',108), CONVERT(datetime, RIGHT(CONVERT(varchar, @timeNow, 100),7),108)))
SELECT DATEADD(minute, @intTime, '')

Result: 887 <- Time in minutes and 1900-01-01 14:47:00.000 <-- Minutes to time

Answer 3

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

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

Answer 4

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)