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