Inconsistant Row Counts When Selecting Data By Dates HELP!

Hi,

I currently think Im going mad!!

I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-

Select Count(*) from Table1 where Added_Date between '1999-12-01'
and '1999-12-31'

or

Select Count(*) from Table1 where Added_Date >= '1999-12-01' and
Added_Date <= '1999-12-31'

For these two above, I get the same result.

HOWEVER!!

If I then do:-

Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00'
and '1999-12-31 23:59:59'
or use the >= and <= operators, the result is larger.

If I then do :-

Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999'
or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.

I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.

Any Ideas as to why this is happening, A'm I being stupid!!!!!!!

(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)

Many thanks,


Peter Farley