Operand data type time is invalid for avg operator...?

Question

How can I avg(time(4)) in the following query:

  select top 10 avg(e.Duration) from TimeTable e

I'm getting the following error:

Operand data type time is invalid for avg operator.

Duration is type time(4) such as:

Duration
-------------
00:00:10.0000

Answer 1

You can use DateDiff( ms, '00:00:00', e.Duration ) to convert the time into an integer number of milliseconds. Use that for your aggregate, then convert the result back, e.g. Cast( DateAdd( ms, 1234, '00:00:00' ) as Time ).

Answer 2

Improving off of HABO answer:

select top 10
Cast(DateAdd( ms,avg(DateDiff( ms, '00:00:00', e.Duration)), '00:00:00' )  as time) as 'avg duration' 
from TimeTable e

Answer 3

Addition to HABO's and Rafi's answers.

For my case, I had to cast the value of the DATEDIFF to a bigint because my value grew too large and caused an arithmetic overflow error.

CAST(DATEADD( ms,AVG(CAST(DATEDIFF( ms, '00:00:00', ISNULL(e.Duration, '00:00:00')) as bigint)), '00:00:00' )  as TIME) as 'avg_time'

Answer 4

Well it seems like time is an invalid type for the avg() method. Refer here for a list of valid data types.

Also, it seems like you need a group of values for this, which would negate the need for top 10 with your current query.