SQL Server: how to avoid between operator with datetime column?

Question

I have a datetime column and I would like to select rows based on this columns. My query is

SELECT *
FROM   dbo.mytable
WHERE  daycol BETWEEN '20110404' AND '20110406';

This will also brings me a row

2011-04-06 00:00:00.000

Which is not correct. How to avoid this? Should between operator be avoided with datetime columns?

Answer 1

You may use this if you don't want the row of 2011-04-06 00:00:00.000 date

SELECT *
FROM   dbo.mytable
WHERE  daycol >= '20110404' AND daycol < '20110406';