average function not operating returning NULL - DATETIME

Question

I am using sqlserver 2005.

I have table tradefile with following important columns :

Scrip_Code : nvarchar(25)
Sauda_Date : datetime   [value example: 3/1/2013 9:15:04 AM]
TradeType  : nvarchar(5)
Market_Rate: float

I wanted to take average of Market Rate for particular sauda_date,scrip_code and tradetype.

For that i fired following query:

select avg(Market_Rate) 
from tradefile 
where 
Scrip_Code='ACC' and 
Sauda_Date =convert(datetime,'03/21/2013')

This query returning me null value.

What is mistake in above query?

Note: sauda_date is datetime and has both time and date as shown in above exmple [3/1/2013 9:15:04 AM]

Please guid me.

score 2 · Accepted Answer · edited May 23 '17 at 12:02

2

SELECT CONVERT(DATETIME,'03/21/2013')

If you run the above, it will print:

2013-03-21 00:00:00.000

So you try to match this to something with a time:

2013-03-21 09:15:04.123

It will obviously not match.

You need to convert both to DATE.

Sauda_Date >= CONVERT(DATETIME, '03/21/2013') AND 
Sauda_Date < DATEADD(dd, 1, CONVERT(DATETIME, '03/21/2013'))

Derived from this answer.

EDIT: Removed CONVERT(DATE, Sauda_Date) approach because DATE is not defined in SQL Server 2005 and because it might be a bad idea.

edited May 23 '17 at 12:02

Community

answered Apr 10 '13 at 11:02

Bernhard Barker

nope not working, in either of the way, should i add something like CONVERT(DATE, '03/21/2013',101) to answer??? – Freelancer Apr 10 '13 at 11:08
@Freelancer I believe adding `101` shouldn't matter since you call the same function on both, and the output is `DATE`, which shouldn't store formatting information. [Here's a test that works](http://www.sqlfiddle.com/#!6/55eed/1). It could help to identify a single row which *should* match and try to figure out why it doesn't. – Bernhard Barker Apr 10 '13 at 11:19
Yeah , on demo its working, in place of DATE i am using datetime since i m with sqlserver 2005. – Freelancer Apr 10 '13 at 11:24
I tried to edit your query and it worked with: CONVERT(varchar(11), Sauda_Date) = CONVERT(datetime, '03/21/2013') – Freelancer Apr 10 '13 at 11:26
just edit your answer with CONVERT(varchar(11), Sauda_Date) = CONVERT(datetime, '03/21/2013') I will mark it as answer. – Freelancer Apr 10 '13 at 11:28
@Freelancer Edited with another solution which should work in SQL Server 2005. – Bernhard Barker Apr 10 '13 at 11:31

score 0 · Answer 2 · answered Apr 10 '13 at 10:44

0

The mistake is that your where clause filtered out all rows, or that Market_Rate was always NULL. Execute the following debug query to confirm:

select * --debug
from tradefile 
where 
Scrip_Code='ACC' and 
Sauda_Date =convert(datetime,'03/21/2013')

answered Apr 10 '13 at 10:44

usr

no this query returns 0 rows, and when i remove Sauda_Date from where then only it returns result. – Freelancer Apr 10 '13 at 10:47
Why do you say "no"? What do you mean by that? It looks like you found the problem?! – usr Apr 10 '13 at 10:51
no means, i wanted to say, query was not operating, sorry, english is not my primary language thats why some mistakes in sentence structure can be there. dont take it wrong. – Freelancer Apr 10 '13 at 10:53
Np. So the query returned no rows - then AVG can also not return anything but NULL. Don't you agree? AVG of zero rows is NULL. – usr Apr 10 '13 at 10:55
yeah, but i can clearly see in my DB that it should return the average of 4 different Marketrates. – Freelancer Apr 10 '13 at 10:57

2 Answers2