Selct distinct returns incorrect results in SQL 2000

Hi, I am seeking desperate help.
We recently upgraded our SQL server from 7.0 TO 2000. Our user are using Bussines objects tools for the reports. When they use the following query " Select distinct (snapshotdate) from tablename group by snapshotdate" returns incorrect results in the meaning of the dates are missing. Whe we use different set of query " that is by using snapshotdate >= date " fundtion it returns correct information. Data is there. Why am I getting this discripence.

I ran update statistics, rebuild indexes. Optimization and integrity everything performed.
SQL 2000 service pack 2 is also applied to the server and client.

All suggestion are welcome. Seeking help sincierly.

Thanks,
Ramakrishna

So what are the two different queries that you are running? What data is it that is "missing" from the distinct query? Why do you use the group by?

In bussiness objects reports tools use distinct function to get the dates.
Here is the query which is giving incorrect results.

"select distinct (snapshotdate) from tablename"

The results from the above query------9/29/2002 0:00
9/30/2002 0:00
10/3/2002 0:00
10/4/2002 0:00
10/7/2002 0:00
10/8/2002 0:00
10/11/2002 0:00
10/12/2002 0:00
10/16/2002 0:00
10/17/2002 0:00
10/20/2002 0:00
10/21/2002 0:00
10/24/2002 0:00
10/25/2002 0:00
10/29/2002 0:00
10/30/2002 0:00
11/2/2002 0:00
11/3/2002 0:00 Please note that some of the dates are missing the query.

The second set of query is ------
"select SnapshotDate
from tabelname
where snapshotdate >= '10/25/2002 00:00:00'
group by snapshotdate"

The results from the above query is----
10/25/2002 0:00
10/26/2002 0:00
10/27/2002 0:00
10/28/2002 0:00
10/29/2002 0:00
10/30/2002 0:00
10/31/2002 0:00
11/1/2002 0:00
11/2/2002 0:00
11/3/2002 0:00
11/4/2002 0:00
11/5/2002 0:00 please note that results with all the dates.

Please, kindly help me to resolve this unexpalinable thing.

Thanks,

I can't explain that either. I've not used business objects. Do you get the same results if you run the queries in query analyzer?

Hi,
Yes, I get the same results on query analyzer. I am not sure what is wrong. I am working on this for past few days and no luck.

I can certainly use any ideas or recomendations with this. I tried everything I can think of.

Need HELP....

Thanks,
Ramakrishna Earasi

The only thing I can think of here is that the distinct comparison is rounding the dates to the nearest day, and therefore that is why you are loosing days that are consecutive.

For example it could be that you have an order on 10/25 and 10/26 but so far as the distinct clause is concerned this could be the same distinct day?

It might be worth testing it with a set of dates in a temporary table.

The same set of queries worked fine on SQL 7.0. This is happening after we upgraded sql server to 2000.

That's most bizarre. If you have space / time I would consider copying data into a different database to rule out database corruption.

try this
select distinct convert(varchar(10),snapshotdate,101) from tablename