How to use AVG aggregation function on datetime fields...

Sunday, May 23, 2004

Today I have been asked by one of my coworkers how to use AVG aggregation function on datetime fields. She asked it because if you use SELECT AVG(MyTable.MyDateTimeField) FROM MyTable, SQL Server will return Server: Msg 409, Level 16, State 2, Line 1 The average aggregate operation cannot take a datetime data type as an argument.

To workaround this, just change the SQL statement to SELECT CAST(AVG(CAST(MyTable.MyDateTimeField AS float)) AS datetime) FROM MyTable and things will work properly.

3 Comments

I was wondering if I can apply this to two fields.... recieved date and completed dated to calculat the average turn around time.

Thanks in advance! BTW awesome blog.

Please shoot me an email j nospace licari at many nospace strengths dot com

Joseph - Monday, August 27, 2007 7:18:27 PM

Joseph,

No problem, just try

SELECT CAST(AVG(CAST(MyTable.MyDateTimeFinish - MyTable.MyDateTimeStart AS float)) AS datetime)
FROM MyTable

Luciano Evaristo Guerche - Monday, August 27, 2007 9:06:17 PM

Hi Luciano Your Code very helping me
Thank's

Aries - Wednesday, October 29, 2008 4:43:37 PM

Comments have been disabled for this content.