Using AVG - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Using AVG

Author

Topic

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-09 : 19:26:01

I want to find the average number of days between two date columns but SQL throws up an error and tells me that I the 'Operand data type datetime is invalid for avg operator.' Here is the code I have as of now:

set @LoDate = '06/01/2012'
set @HiDate = '06/30/2012'

select AVG(RCV.RCV_DATE - INV.PMT_DATE)

from INV
JOIN PO
on PO.PO_ID = INV.PO_ID

JOIN RCV
on RCV.PO_ID = PO.PO_ID

where INV.INV_DATE >= '06/01/2012' and
INV.INV_DATE <= '06/30/2012'

group by
RCV.RCV_DATE,
INV.PMT_DATE

I also tried select AVG(SUM(RCV.RCV_DATE - INV.PMT_DATE)) and I got the same error message about SUM.

Is there a way to do this?

Thanks
Sherri

SLReid
Forum Newbie
Renton, WA USA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-07-09 : 19:37:03

What do you mean by average number of days between two dates? That doesn't make sense. Do you just want the count of days? Have you looked at DATEDIFF?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-09 : 19:39:14

The subtraction operator when used on DATATYPE yields a result that is also of DATETIME type. And, you cannot take the average of that. If you really want the average number of days, use the datediff function to find the difference between the two dates, and take the average of that:

select AVG(DATEDIFF(day, INV.PMT_DATE, RCV.RCV_DATE))

Note that I have PMT_DATE as the second parameter and RCV_DATE as the third parameter. That may not be what you want - if not, simply swap them.

sherrireid
Yak Posting Veteran

58 Posts

Posted - 2012-07-10 : 13:59:36

Sunitabeck! Thank you so much! That was exactly what I was looking for. I appreciate your help.

Regards
Sherri Reid

SLReid
Forum Newbie
Renton, WA USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources