sum time field in sql .result will get hour,minute & sec format

Question

EarlyLeaveHrs time(7) -- how to sum time field in sql Data 01:18:00.0000000 04:56:00.0000000 00:00:00.0000000 00:15:00.0000000 00:55:00.0000000 reesult will get - 00:00:00 - this format

Jithu Thomas · Answer

WITH TimeData AS ( SELECT CAST('01:18:00.0000000' AS TIME(7)) AS EarlyLeaveHrs UNION ALL SELECT CAST('04:56:00.0000000' AS TIME(7)) UNION ALL SELECT CAST('00:00:00.0000000' AS TIME(7)) UNION ALL SELECT CAST('00:15:00.0000000' AS TIME(7)) UNION ALL SELECT CAST('00:55:00.0000000' AS TIME(7)) ), SecondsData AS ( SELECT DATEPART(HOUR, EarlyLeaveHrs) * 3600 + DATEPART(MINUTE, EarlyLeaveHrs) * 60 + DATEPART(SECOND, EarlyLeaveHrs) AS TotalSeconds FROM TimeData ), SumSeconds AS ( SELECT SUM(TotalSeconds) AS SumTotalSeconds FROM SecondsData ) SELECT RIGHT('0' + CAST(SumTotalSeconds / 3600 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST((SumTotalSeconds % 3600) / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(SumTotalSeconds % 60 AS VARCHAR), 2) AS TotalTime FROM SumSeconds; 1. Convert the time to seconds. 2. Sum the seconds. 3. Convert the summed seconds back to the `HH:mm:ss` format.

Nikunj Satasiya · Answer

Hi Lejo, Can you try this: SELECT CAST(DATEADD(SECOND, SUM(DATEDIFF(SECOND, '00:00:00', EarlyLeaveHrs)), '00:00:00') AS TIME) AS TotalTime FROM YourTableName;

Chetan Sanghani · Answer

Try this query: WITH TimeData AS ( SELECT DATEDIFF(SECOND, '00:00:00', EarlyLeaveHrs) AS Seconds FROM YourTable ), TotalSeconds AS ( SELECT SUM(Seconds) AS TotalSeconds FROM TimeData ) SELECT RIGHT('0' + CAST(TotalSeconds / 3600 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST((TotalSeconds % 3600) / 60 AS VARCHAR), 2) + ':' + RIGHT('0' + CAST(TotalSeconds % 60 AS VARCHAR), 2) AS TotalTime FROM TotalSeconds;

Naveen Kumar · Answer

To sum TIMEandHH:MM:SS format, you can use a combination of SQL functions. SQL Server does not directly support summing TIMEpainTIME is designed for a specific point in the day rather than a duration. However, you can convert the TIME values to seconds, sum them, and then convert the result back to the HH:MM:SS format. Here's a step-by-step approach to achieve this: Convert TIME to seconds. Sum the seconds. Convert the total seconds back to HH:MM:SS. Here’s how you can do it in SQL Server: -- Sample data table DECLARE @TimeData TABLE (EarlyLeaveHrs TIME(7)); -- Insert sample data INSERT INTO @TimeData (EarlyLeaveHrs) VALUES ('01:18:00.0000000'), ('04:56:00.0000000'), ('00:00:00.0000000'), ('00:15:00.0000000'), ('00:55:00.0000000'); -- Sum times and convert to HH:MM:SS SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, SUM(DATEDIFF(SECOND, '00:00:00', EarlyLeaveHrs)), '00:00:00'), 108) AS TotalTime FROM @TimeData;