Time cannot be summed directly in T-SQL. In order to sum two times they first need to be assigned a date. When a time data type is cast as a datetime data type, as it does not have a date element, the value defaults to the date of 1900-01-01.
As T-SQL does have the functionality to sum datetime and as the date element will be the same only the time value will be summed. This functionality allows us to sum time.
Below is example T-SQL:
IF OBJECT_ID('tempdb..#TimeTable', 'U') IS NOT NULL BEGIN DROP TABLE #TimeTable END CREATE TABLE #TimeTable( id INT ,TimeRecord TIME(0) ); INSERT INTO #TimeTable VALUES ( 1 ,'00:00:10' ); INSERT INTO #TimeTable VALUES ( 1 ,'00:14:00' ); INSERT INTO #TimeTable VALUES ( 2 ,'00:00:10' ); INSERT INTO #TimeTable VALUES ( 2 ,'00:35:10' ); SELECT id ,TimeRecord FROM #TimeTable; /*demo of time converted to datetime*/ SELECT CAST(TimeRecord AS DATETIME) AS DateTimeRecord FROM #TimeTable SELECT id ,CAST(DATEADD(MILLISECOND, SUM(DATEDIFF(MILLISECOND, 0, CAST(TimeRecord AS DATETIME))), 0) AS TIME(0)) AS SummedTime FROM #TimeTable GROUP BY id;
Tidbytez 