MySQL SELECT WHERE datetime matches day and not necessarily time

Question

I have a table which contains a datetime column   I wish to return all records of a given day regardless of the time   Or in other words  if my table only contained the following 4 records  then only the 2nd and 3rd would be returned if I limit to 2012-12-25   2012-12-24 00 00 00 2012-12-25 00 00 00 2012-12-25 06 00 00 2012-12-26 05 00 00

User · Answer

SELECT   FROM table where Date col     date

User · Answer

You can use      SELECT   FROM datetable WHERE datecol LIKE  2012-12-25

User · Answer

WHERE date column  gt   2012-12-25  AND date column  lt  2012-12-26  may potentially work better if you have an index on date column  than DATE

User · Answer

NEVER EVER use a selector like DATE datecolumns     2012-12-24  - it is a performance killer    it will calculate DATE   for all rows  including those  that don t match it will make it impossible to use an index for the query   It is much faster to use  SELECT   FROM tablename  WHERE columname BETWEEN  2012-12-25 00 00 00  AND  2012-12-25 23 59 59    as this will allow index use without calculation   EDIT  As pointed out by Used By Already  in the time since the inital answer in 2012  there have emerged versions of MySQL  where using  23 59 59  as a day end is no longer safe  An updated version should read  SELECT   FROM tablename  WHERE columname  gt   2012-12-25 00 00 00  AND columname  lt  2012-12-26 00 00 00    The gist of the answer  i e  the avoidance of a selector on a calculated expression  of course still stands

[mysql] MySQL SELECT WHERE datetime matches day (and not necessarily time)

Examples related to mysql

Examples related to date

Examples related to select