Invalid value for operand data type for avg ...?

Question

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

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

I get the following error:

Invalid operand data type value for the avg operator.

Duration is a time type (4), for example:

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

Answer 1

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

Answer 2

HABO response improvement:

 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

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

Also, it seems that you need a group of values ​​for this, which negates the need for top 10 in your current query.

Answer 4

Addition to HABO and Rafi is responsible.

For my case, I had to specify the DATEDIFF value for bigint, because my value became 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'