Sum or time Over 24 Hours or Hrs Or Sum of Time Greater than 24 Hrs or Hours

Original Link

DECLARE @Sample TABLE
(
data TIME
)

INSERT @Sample
SELECT '23:30' UNION ALL
SELECT '22:30' UNION ALL
SELECT '16:00'

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, theHours + theMinutes, '19000101'), 8), 1, 2, CAST((theHours + theMinutes) / 3600 AS VARCHAR(12)))
FROM
    ( SELECT ABS(SUM(
                    CASE CHARINDEX(':', CAST(data AS VARCHAR))
                            WHEN 0
                            THEN 0
                            ELSE 3600 * LEFT(CAST(data AS VARCHAR), CHARINDEX(':', CAST(data AS VARCHAR)) - 1)
                    END)) AS theHours,
            ABS(SUM(
                    CASE CHARINDEX(':', CAST(data AS VARCHAR))
                            WHEN 0
                            THEN 0
                            ELSE 60 * SUBSTRING(CAST(data AS VARCHAR), CHARINDEX(':', CAST(data AS VARCHAR)) + 1, 2)
                    END)) AS theMinutes
    FROM    @Sample
    ) AS d

This is only when dataType is Time we have to cast the column to VARCHAR

The above query is rewritten as like this

SELECT CASE
        WHEN totalhours IS NOT NULL
        THEN Stuff(Convert(CHAR(8),Dateadd(SECOND,totalhours,'19000101'),
                           8),1,2,Cast(totalhours / 3600 AS VARCHAR(12)))
        ELSE NULL
      END
FROM   (SELECT CASE
                WHEN (Abs(Sum(Datediff(SECOND,'00:00',data)))) > 0
                THEN Abs(Sum(Datediff(SECOND,'00:00',data)))
                ELSE NULL
              END AS totalhours
       FROM   @Sample) AS d

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)

Add '0' to Fixed precision and add '0' to scale numbers

DECLARE @PadChar CHAR(1)
DECLARE @FinalLength INTEGER
DECLARE @SourceString VARCHAR(19)
SET @PadChar = '0'
SET @FinalLength = 5

--Which Gives 2 decimal Digits
SELECT CONVERT(VARCHAR(10),CONVERT(MONEY,2))

--The follwing which adds Fixed precision(if there is 1 digit,
--it adds 0 to previous of the 1 and gives 01.00 as ouput)
-- The output is in the format of 00.00

SET @SourceString = '2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '2.0'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '0.2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '00.2'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SET @SourceString = '0.20'
SELECT RIGHT( REPLICATE(@PadChar,@FinalLength - LEN(@FinalLength)) +
CONVERT(VARCHAR(10),CONVERT(MONEY,@SourceString)), 5)

SUM of time

SELECT CAST(DATEADD(millisecond,
SUM(DATEDIFF(millisecond,0,CAST(totalhrs AS DATETIME))),0) AS TIME)
FROM     TABLE