mysql between 没有值,MySQL-BETWEEN将不会选择正确的结果

I am trying to select rows that are in between two dates. First, here is my data:

punch_id eid time unixtime punch_type day date doy

135 2 12:53 1314723193 0 4 08/28/2011 241

134 2 12:53 1314723190 3 4 08/31/2011 241

133 2 12:53 1314723187 2 4 08/20/2011 241

132 2 12:52 1314723125 1 4 08/30/2011 241

I have tried these two queries.

SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` <= '08/31/11'

SELECT * FROM `punches` WHERE `date` BETWEEN '08/20/11' AND '08/31/11'

Neither of these select the rows containing the date 08/31/11. It selects the 08/20/11 ones though. I tried to use it another way and when I run the query:

SELECT * FROM `punches` WHERE `date` >= '08/10/11' AND `date` <= '08/20/11'

I again do not get the correct result: the 20th is left out once again. What is the problem with the way I am executing this?

解决方案

See this related question.

As others have mentioned, your primary problem is not accounting for the time. A few options to handle that:

Use a function to convert the DateTime to a Date. I don't recommend this option, since it will likely make the function non-sargeable.

Expand your BETWEEN to explicitly include the last moment of the day: (note: this is the latest possible value that MS SQL can store, don't know if MySQL has the same value)

SELECT * FROM `punches` WHERE `date`

BETWEEN '08/20/11 00:00:00.000' AND '08/31/11 23:59:59.997'

Use a < for the upper value

SELECT * FROM `punches` WHERE `date` >= '08/20/11' AND `date` < '09/01/11'

I actually think that last one is easier, in most situations.

I suppose you could do other things, like change the datatype of the column, but I've assumed here that you're just interested in changing the query.

** Disclaimer: I'm a MS SQL guy, not MySQL