MySQL compare DATE string with string from DATETIME field

Manny Calavera picture Manny Calavera · May 3, 2010 · Viewed 199.4k times · Source

I have a question: Is it possible to select from a MySQL database by comparing one DATE string "2010-04-29" against strings that are stored as DATETIME (2010-04-29 10:00)?

I have one date picker that filters data and I would like to query the table by the DATETIME field like this:

SELECT * FROM `calendar` WHERE startTime = '2010-04-29'"

...and I would like to get the row that has the DATETIME value of "2010-04-29 10:00".

Any suggestions? Thanks.

Answer

David picture David · Jul 2, 2013

Use the following:

SELECT * FROM `calendar` WHERE DATE(startTime) = '2010-04-29'

Just for reference I have a 2 million record table, I ran a similar query. Salils answer took 4.48 seconds, the above took 2.25 seconds.

So if the table is BIG I would suggest this rather.