T-SQL Calculate average value for Duration Field

Can anyone please help me on this..

I have a column in my table that stores the duration in an nvarchar field formatted as '00:00:00'.

How do I calculate the average duration? I read some other blogs and also used cast techniques like the one below but couldn't solve my problem...

cast(cast(avg(cast(CAST(Duration as datetime) as float)) as datetime) as time) AvgTime

Please help...

Store duration in seconds as an integer; then average is quite easy. Right now you're trying to take an average of a string converted to a float converted to a datetime converted to a time. If that doesn't sound wrong to you, read it again. Then consider that time represents a point time. What is the average of 3:12 AM and 4:57 PM? Meet in the middle?

Don't be tempted to change this from nvarchar to time. Again, time is a point in time, not an interval. You should be storing start and end values as datetime; you can always calculate duration from that (typically I see duration as an additional nonsense column that doesn't need to be stored in the first place).

The formatting as hh:mm:ss should not happen at storage time, only at presentation time. And no idea why you would ever use nvarchar for this in the first place - what Unicode characters do you expect to support in hh:mm:ss?

In the meantime:

SELECT CONVERT(TIME(0), DATEADD(SECOND, AVG(DATEDIFF(SECOND, 0, CONVERT(DATETIME, 
  CASE WHEN ISDATE(Duration) = 1 THEN Duration END))),0)) -- in case of garbage data 
FROM dbo.your_table_name;

See how messy that is? Wouldn't it be much easier as:

SELECT AVG(Duration) FROM dbo.your_table_name;

Or:

SELECT AVG(DATEDIFF(SECOND, [start], [end])) FROM dbo.your_table_name;

And then format as hh:mm:ss in the client language? Yes, I think so.

StackExchange Database Administrators Q#68440, answer score: 7