How to convert the integer to Time using SQL query ???

[Former Member]

Hi Experts,         

  How to convert the integer to Time using SQL query ???

For Ex,

Integer Format               Time Format

     1115              =>         11:15 AM

Regards,

K.Ramasamy

[former_member143715 · Accepted solution]

try this

SELECT CONVERT( TIME, STUFF( RIGHT( '0000' + CONVERT( VARCHAR(5), '1115'), 4), 3, 0, ':'))

[Former Member · Accepted solution]

Mokthar ,     Also I need the difference between two dates &  time using the above query .. Regards, K.Ramasamy

[former_member143715 · Accepted solution]

you must use datediff :

SELECT DATEDIFF(hh, '01/08/2015 12:00', '04/08/2015 00:00')

[Former Member · Accepted solution]

mokhtar     I am having date in separate field & Time in separate field..Then Hw can split up the Difference ?? Regards, K.Ramasamy

[former_member143715 · Accepted solution]

SELECT DATEDIFF(hh, convert(datetime,'01/08/2015 '+'12:00'),convert(datetime,'04/08/2015 '+'00:00') )

[Former Member]

hi ashish Do u know how to calculate difference hour between two date & time excluding holidays ?? Regards, K.Ramasamy

[Former Member]

Hi Ramasamy,

I believe this is different from the parent question.

if the first question is solved, then you should close this thread and open a new thread for different question.

Thanks

Ashish Ranjan

[Former Member]

hi frank ,       i dnt knw how to use the function...can u explain wat to be done Regards, K.Ramasamy

[frank_wang6]

CREATE FUNCTION [dbo].[ConvertSapTS2TimeString]

(

  -- Add the parameters for the function here

  @SapTS int

)

RETURNS varchar(8)

AS

BEGIN

  -- Declare the return variable here

  DECLARE @TimeString varchar(8)

  DECLARE @Str varchar(6);

  -- Add the T-SQL statements to compute the return value here

  SET @Str = CAST(@SapTS AS VARCHAR(6));

  IF @SapTS < 100000

  BEGIN

  SET @TimeString = '0' + SUBSTRING(@STR, 1, 1) + ':' + SUBSTRING(@STR, 2, 2) + ':' + RIGHT(@STR, 2);

  END

  ELSE

  BEGIN

  SET @TimeString = LEFT(@STR, 2) + ':' + SUBSTRING(@STR, 3, 2) + ':' + RIGHT(@STR, 2);

  END;

  -- Return the result of the function

  RETURN @TimeString

END

[Former Member]

Hi Ramasamy,

What is the length of integer value you are looking for ?

if you are looking for 8 chars, below is the code which can convert integer to time.

declare @T int

set @T = 10455836 --8 chars

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

Above code you can adjust as per the integer length.

let me know if you need more assistance.

Regards

Ashish Ranjan

[Former Member]

Hi Ashish    

Integer value will be 4 characters

Regards,

K.Ramasamy

[Former Member]

Hi Ramasamy,

Use the code below for 4 chars:

-------------------------------

declare @T int

set @T = 1115 --4 chars

select dateadd(hour, (@T / 100) % 100,

  dateadd(minute, (@T / 1) % 100,

  dateadd(second, (0000) % 100,

  dateadd(millisecond, (0000) * 10, cast('00:00:00' as time(2))))))

--------------------------

Output is: 11:15:00:00

Regards

Ashish Ranjan