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.

Seattle

I don't know the time format they have used, but here is an example 10455836. – Seattle

You need to know what that number means before anybody can tell you how to convert it to a time. – Choirmaster

What range are the values, any > 86400000? can you create a record at a known time and show both? – Ibson

I examined the entire table there is no value exceeding 86400000. – Seattle

random suggestion ms since midnight? dateadd(millisecond,10455836,'00:00:00') – Ibson

Jim, I obtained the result as 02:54:15.8370000. 00 represents 7 digit milliseconds – Seattle

Now I found the time format as HH:MM:SS:00 means that 10455836 represents 10:45:58:36 similarly 421151 means 00:42:11:51. Can anyone help on this to convert the integer to time. – Seattle

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)

Guardafui

Additional info according to time datatype can be fond here: technet.microsoft.com/en-us/library/bb677243.aspx – Parsley

It's 4 years and a half later, but this should be accepted as the answer. It's exactly what I needed – Archimage

Answer 2

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)

Moresque

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/

Kerseymere

@chŝdk This may be a terse answer, but it is "Use the built-in MSDB.DBO.AGENT_DATETIME(20150119,0)", the link is just for support. That makes it not a link-only answer. – Capping

@MarkRotteveel While what you are saying is true, the answer lacks of explanation, description and formatting. – Kozhikode

@chŝdk That might be reason to downvote, not to just delete. – Capping

@MarkRotteveel The first purpose of SO, is to give and keep only pertinent answers and posts and not only voting or blaming, if the answer is worth of being here, it would be here :) – Kozhikode

@cнŝdk And given that the answer doesn't meet any of the criteria for deletion, the answer is worthy of being here. That you think the answer merits a downvote isn't cause to vote to delete the answer for a reason that doesn't apply. – Kyat

@Kyat I never said it merits a downvote, I just said it should be well formatted to avoid having a link only answer or a low quality post. – Kozhikode

@cнŝdk But it's not a link only answer, as has already been said. Adding a link to an answer doesn't turn it into a link only answer. A link only answer is an answer that's only a link, not a link added to an answer. And you saying you think the post is low quality is a reason to downvote. It's not a reason to delete a post. It's literally precisely why downvotes exist. – Kyat

Another time I am not the one who flagged the answer, I just got it in the review list and I said it should be better formatted, and includes explanation/description :) – Kozhikode

I personally like this answer because I'm too lazy to write my own function. :) However, this answer does not address the OP's question. The OP asked for help converting a time represented as an integer into the "HH:MM:SS:00" format. This answer addresses the date portion, not the time portion. Instead of "0", you would need to pass the time value as well as the date, e.g., "[msdb].[dbo].[agent_datetime](20150119,114732)". Furthermore, [agent_datetime] does not support fractions of a second and the OP's question specifically includes nanoseconds in the desired time format. – Sanguinary

Answer 4

Just saying this is the easiest approach and since the value is the actual value just not in time format visually. Without any additional complications, below is the quickest-to-the-curb result.

declare @val int = 23421155
declare @strTime varchar(25)

select
    @strTime = format(@val, '00:00:00:00')

select
    @strTime
    , cast(@strTime as Time)

Produces:

23:42:11:55
23:42:11.0550000

Uremia

4 digit HH:MM works too. select cast(format(1532, '00:00') as time); 15:32:00.0000000 – Vanzant

@Vanzant true....but their number was more HH:MM:SS:MS....OP didn't seem to want the Milli-Seconds to be represented in Full Decimal, and only the first 100th of a second to be represented. – Uremia

Yeah, it was more of a note to future me when I inevitably come back to this question. I have a legacy db with a time that's an int with only HHMM. – Vanzant

Answer 5

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)

Garretgarreth

Your code returns different results than expected by OP – Scabious

Answer 6

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

Canonicity

Answer 7

CREATE FUNCTION [dbo].[_ICAN_FN_IntToTime](@Num INT)
  RETURNS NVARCHAR(13)
AS
-------------------------------------------------------------------------------------------------------------------
--INVENTIVE:Keyvan ARYAEE-MOEEN
-------------------------------------------------------------------------------------------------------------------
  BEGIN
    DECLARE @Hour VARCHAR(10)=CAST(@Num/3600 AS  VARCHAR(2))
    DECLARE @Minute VARCHAR(10)=CAST((@Num-@Hour*3600)/60 AS  VARCHAR(2))
    DECLARE @Time VARCHAR(13)=CASE WHEN @Hour<10 THEN '0'+@Hour ELSE @Hour END+':'+CASE WHEN @Minute<10 THEN '0'+@Minute ELSE @Minute END+':00.000'
    RETURN @Time
  END
-------------------------------------------------------------------------------------------------------------------
--SELECT dbo._ICAN_FN_IntToTime(25500)
-------------------------------------------------------------------------------------------------------------------

Sojourn

little explanation to your will definitively help better understand your solution – Binate

Answer 8

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

DECLARE @ss INT = 6389
select convert(TIME(0), dateadd(ss, @ss, '00:00:00') ) AS [HH:mm:ss]

Edinburgh