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

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;

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;

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;

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.