Finally I found the mistake I had made. Thanks for Itzik Ben-Gan who has given the clear explanation of this. How do you filter the data for a particular time frame (say for 01^st of Jan to 10^th of Jan)? You have mainly two options;

SELECT SomeColumn FROM table1 WHERE DateCoulm BETWEEN ‘01/01/2006 00:00:00:000’ AND ‘01/10/2006 23:59:59:999’.
SELECT SomeColumn FROM table1 WHERE DateCoulm >= ‘01/01/2006 00:00:00:000’ AND DateCoulm <= ‘01/10/2006 23:59:59:999’.

If you know the issue, of course this is not the way of writing this. The issue is, if the table contains a record that the DateColumn value is ‘01/11/2006 00:00:00:000’, it will be one of the records in the result-set that is not what we expect. The reason for this is, when converting strings to datetime, the value will be rounded to the nearest three-hundredth of a second. So, in the above TSQL, the value of ‘01/10/2006 23:59:59:999’ is converted to the ‘01/11/2006 00:00:00:000’ that causes to qualify even ‘01/11/2006’ dated records.

We can correct the problem by changing the millisecond value to 997;

SELECT SomeColumn FROM table1 WHERE DateCoulm BETWEEN ‘01/01/2006 00:00:00:000’ AND ‘01/10/2006 23:59:59:997’.

but I think that most appropriate solution is;

SELECT SomeColumn FROM table1 WHERE DateCoulm >= ‘01/01/2006 00:00:00:000’ AND DateCoulm <= ‘01/11/2006 00:00:00:000’.

Note that this problem exist only with datetime not with smalldatetime.

SQL Server 2005 Servise Pack 2 - Community Technolodgy Perview is now avaialble for download. Download from here.