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.
精彩评论