MySQL date comparison function

Question

In my table dates are stored in the format "2010-08-26 09:00:00" so yyyy-mm-dd. I'd like to select all fields that have a date field that is "today", so between 2010-08-26 00:00:01 and 2010-08-26 23:59:59.

Just using larger and smaller than operators does not work, does MySQL have some built in date comparison functions that can do this?

EDIT: I should have mentioned dates are stored as datetime fields

Answer 1

use function DATE()

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

Answer 2

Using the DATE() function you should be able to construct a value for comparison:

DATE(2010-08-26 12:13:24)

will return just the date section for you ie. 2010-08-26.

Answer 3

I'd suggest [...] WHERE DATEDIFF(CURDATE(),date_field) = 0 [...]

Answer 4

Just a recommendation for the future: storing a date in a varchar column is almost always a very bad idea.

Answer 5

... WHERE DATE(field)=CURDATE()

or

... WHERE DATE(field)=CURRENT_DATE

or

... WHERE DATE(field)=CURRENT_DATE()

Pick one!