MySQL compare DATE string with string from DATETIME field.

Question

Hello. I have a question: Is it possible to select form 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 1

SELECT * FROM `calendar` WHERE startTime like '2010-04-29%'

You can also use comparison operators on MySQL dates if you want to find something after or before. This is because they are written in such a way (largest value to smallest with leading zeros) that a simple string sort will sort them correctly.

Answer 2

SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"