--Sample Data DECLARE @tab TABLE( date DATETIME , spendtime TIME)
INSERT INTO @tab
SELECT '2013-01-08 00:00:00.000', '03:20:00' union all
SELECT '2013-01-08 00:00:00.000', '01:19:00' union all
SELECT '2013-01-09 00:00:00.000', '04:33:00'
/*
Expected output :
-------------date------------spendtime
2013-01-08 00:00:00.000-----04:39
2013-01-09 00:00:00.000-----04:33
*/
--Method 1 SELECT DATE,
CAST( DATEADD(s, SUM(DATEDIFF(s, 0, spendtime)), 0) AS TIME)
FROM @tab
GROUP BY DATE
-- Method2 ;WITH CTE
AS (SELECT [date], SUM(DATEDIFF(s, 0, spendtime)) TotalSecond
FROM @tab
GROUP BY [date]
)
SELECT DATE
, CONVERT(VARCHAR(10),TotalSecond/3600)+ '.' +
CONVERT(VARCHAR(20),TotalSecond%3600/60) + '.' +
CONVERT(VARCHAR(20),TotalSecond%3600%60) AS [TotalTime]
FROM CTE