SUM up the datetime column

Posted by Bandi under Sql Server category on | Points: 40 | Views : 260
Summing a datetime column is not possible as SUM() aggregate expects the numeric value ( INT, DECIMAL columns)...

DECLARE @SumDateTime TABLE( DateTimeColumn DATETIME)
INSERT @SumDateTime
SELECT '1900-01-01 10:55:40.000' union all
SELECT '1900-01-01 14:35:36.000' union all
SELECT '1900-01-01 15:15:10.000' union all
SELECT '1900-01-01 21:42:30.000'

SELECT SUM(DateTimeColumn) FROM @SumDateTime

gives below error :
Msg 8117, Level 16, State 1, Line 10
Operand data type datetime is invalid for sum operator.

The intension of above code is to sum the time.... To do so,

SELECT DATEADD( MI, SUM(datediff(Mi, '1900-01-01',DateTimeColumn)),'1900-01-01') FROM @SumDateTime

Output is:
1900-01-03 14:27:00.000

Comments or Responses

Login to post response