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.
This question is related to
sql
sql-server-2008
date
time
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)
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)
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/
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)
-------------------------------------------------------------------------------------------------------------------
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
Source: Stackoverflow.com