Here's the scenario. The column in question is called 'datein' and it's type is 'datetime'. I have three rows with the value of '2009-10-01 00:00:00.000' for 'datein'. Why does this query return the aforementioned rows?
SELECT * FROM t_call AS tc WHERE tc.datein >= '2009-09-30 00:00:00.000' AND tc.datein <= '2009-09-30 23:59:59.999'
SELECT * FROM t_call AS tc WHERE tc.datein BETWEEN '2009-09-30 00:00:00.000' AND '2009-09-30 23:59:59.999'
returns the same result
It's the lack of precision in the thousandths of a second value. Try ".997" instead.
Run this and you'll see:
declare @dt datetime select @dt = '2009-09-30 23:59:59.999' select @dt