The answer is incorrect since 2009-01-30 00:00:00:000 will not be returned. But you are pointing out a very important issue of SQL Server, datetime values are rounded to increments of .000, .003, or .007 seconds even in insert/update statements.

If you execute SELECT InsertedOn FROM MyLogs you will see will see that '2009-01-31 23:59:59:999' are converted to '2009-02-01 00:00:00:000' when they were inserted.

If you use SQL Server 2008, there is a new datatype datetime2 with a precision of 100 nanoseconds. And if you try the same sample with datetime2 datatype you will end up with the correct result. But in the meantime, my recommendation is to not use BETWEEN in SQL Server 2005 for datetime values because of this side effects.

Håkan Winther

Senior development DBA