CODEWITHSUNDEEP
sqlsql-server-2008datetime
Praveen
Praveen

Reputation: 56539

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

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.

Upvotes: 28

Views: 221517

Answers (8)

Zahar
Zahar

Reputation: 11

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]

Upvotes: 1

GoldBishop
GoldBishop

Reputation: 2861

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

Upvotes: 6

user13359037
user13359037

Reputation: 11

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)
-------------------------------------------------------------------------------------------------------------------

Upvotes: 1

Wilfred van Dijk
Wilfred van Dijk

Reputation: 99

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)

Upvotes: 4

Bruce Morris II
Bruce Morris II

Reputation: 89

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

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

Upvotes: 6

all about data
all about data

Reputation: 509

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)

Upvotes: 13

A Ghazal
A Ghazal

Reputation: 2843

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

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 139010

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)

Upvotes: 25

Related Questions