SQL Server 2005 DateTime math

I'm trying to get the amount of time spent on something. I have the DateTime for the start and end of the thing in two columns. I can do:

 cnext.CreatedDate - c.CreatedDate

which results in something like this:

 1900-01-01 00:00:19.190

which means they spent 19.19 seconds on the thing. That's great for each row but i would like to group by the specific thing and sum up the time spent on each thing:

SUM(cnext.CreatedDate - c.CreatedDate)

but I get a:

Operand data type datetime is开发者_运维问答 invalid for sum operator.

How should i go about getting the total time is I can't sum up DateTime types?

You can't SUM datetime values. This is like taking the square root of a string.

Try:

SUM(DATEDIFF(SECOND, c.CreatedDate, cnext.CreatedDate))

Or if you want greater precision:

SUM(DATEDIFF(MILLISECOND, c.CreatedDate, cnext.CreatedDate))

If the date potion is 1/1/1900, convert the dates to floats, subtract them, and convert the result back to datetime.