Aggregate smalldatetime fields in stored procedures

[Guest]

Friends,

I have a simple SQL statement in my querybuilder. It goes like this:

SELECT operations_code, operations_duration
FROM dpr_operations_report_line
WHERE id = @id

The result is like this:
operations_code operations_duration
O 02:05:00
O 00:10:00
T 21:44:00

The duration field type is smalldatetime. The challenge is that I want to
sum the operation_duration grouped by the operations_code to get the total
time pr operations code. The SUM functions does not be applied on a
smalldatetime field. Is there a workaround?

/Leif S.

 

[John Spencer]

Part of your problem is that you are storing duration as a point in time.
When you try to SUM a point in time, it adds days and times together to give
you a "number" that represents a day and time and not a duration.

SmallDateTime is an MS SQL field type. I think you would have to convert
the datetime to a number of second or minutes and then sum that. Then you
could use math to create a string that would look like hours and minutes.