MySQL query date ranges?

I'm trying to pull records from a range of datetime fields. The results are coming together as expected, however the high range doesn't display any results. So if I query records between 2008-05-04 and 2008-05-08 the records stop without displaying the 2008-05-08 records, however anything beween 05 and 07 will show up...weird.

Here's my query, i've tried many variations and they all have the same result


SELECT * FROM timecard WHERE time_in >= '2008-05-03' AND time_in <= '2008-05-08'


I could put a bump into my php to add a day to the date, but I'd rather figure out what I'm doing wrong and fix it within the query

any help will be much appreciated - thanks!

use BETWEEN keyword

It looks like your time_in field is DATETIME, which means that all values falling into May 8, will not match by original condition, because the engine casts 2008-05-08 to "2008-05-08 00:00:00" before it can compare the value to your time_in (i.e. midnight on that date).

You got to change your query to this:

SELECT * FROM timecard WHERE time_in >= '2008-05-03 00:00:00' AND time_in <= '2008-05-08 23:59:59'

Or, add 1 day to the upper limit, as you suggested yourself.