Convert Numeric to Time Convert VarChar to Time

If our input string or numeric does not in the form of HH:MM then

Do the stuff in this link



SELECT

--Adds 2 zeros as the end(it decimal places)
CONVERT(VARCHAR(18), Hours),
-- Which adds 0 to the Fixed precision
-- Took only 5 char from the RIGHT postion

RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5),

--Replace . with :
--It looks like time in the format of hh:mm:ss

REPLACE(RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),

--Convert the varchar format to DATETIME

--CONVERT(DATETIME, REPLACE(RIGHT( REPLICATE('0',4) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'), 108)
--CAST(REPLACE(RIGHT( CONVERT(VARCHAR(16),(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108) - DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0)),108), 5),':','.')AS NUMERIC(4,2))

--Cast varchar to DateTime
CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108),

--Cast DateTime to Time

CAST(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108)AS TIME),

--Cast numeric to varchar by replaceing . with : and cast that to time and sum the time
CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0) AS TIME),


--Subtract a time from another time

CONVERT(CHAR(5), CAST(

CAST(CONVERT(DATETIME,REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), Hours), 5) + ':00', '.', ':'),108)AS TIME)

AS DATETIME) - CAST(

CAST(DATEADD(millisecond,SUM(DATEDIFF(millisecond,0,CAST(TotalHrs AS DATETIME))),0) AS TIME)
AS DATETIME), 108)