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

User · Answer

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)

User · Answer

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 lt 10 THEN  0   Hour ELSE  Hour END     CASE WHEN  Minute lt 10 THEN  0   Minute ELSE  Minute END   00 000      RETURN  Time   END ------------------------------------------------------------------------------------------------------------------- --SELECT dbo  ICAN FN IntToTime 25500  -------------------------------------------------------------------------------------------------------------------

User · Answer

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

User · Answer

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

User · Answer

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  lt - Time in minutes and  1900-01-01 14 47 00 000  lt -- Minutes to time

User · Answer

Use the built-in MSDB DBO AGENT DATETIME 20150119 0   https   blog sqlauthority com 2015 03 13 sql-server-interesting-function-agent datetime

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

Examples related to sql

Examples related to sql-server-2008

Examples related to date

Examples related to time