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

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?

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';

If you write out this line:

WHERE daycol BETWEEN '20110404' AND '20110406';

You get:

WHERE '2011-04-04 00:00:00.000' <= daycol 
      and daycol <= '2011-04-06 00:00:00.000'

Between is inclusive. If you'd like to exclude the boundaries, change <= to < in the expanded version.

Please note this is the expected behavior of BETWEEN
(See: http://msdn.microsoft.com/en-us/library/ms187922.aspx)

From the article:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

You can modify your end_expression to get different results than you are receiving.

Or use >=, < operators. But be careful if the column you are comparing against is DATETIME T-SQL will convert '20110406' to '2011-04-06 00:00:00.000' so take this into consideration when determining which operators to use.