MySQL compare DATE string with string from DATETIME field

Question

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 1

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.

Answer 2

If you want to select all rows where the DATE part of a DATETIME column matches a certain literal, you cannot do it like so:

WHERE startTime = '2010-04-29'

because MySQL cannot compare a DATE and a DATETIME directly. What MySQL does, it extends the given DATE literal with the time '00:00:00'. So your condition becomes

WHERE startTime = '2010-04-29 00:00:00'

Certainly not what you want!

The condition is a range and hence it should be given as range. There are several possibilities:

WHERE startTime BETWEEN '2010-04-29 00:00:00' AND '2010-04-29 23:59:59'
WHERE startTime >= '2010-04-29' AND startTime < ('2010-04-29' + INTERVAL 1 DAY)

There is a tiny possibility for the first to be wrong - when your DATETIME column uses subsecond resolution and there is an appointment at 23:59:59 + epsilon. In general I suggest to use the second variant.

Both variants can use an index on startTime which will become important when the table grows.

Answer 3

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

OR

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

Answer 4

SELECT * FROM sample_table WHERE last_visit = DATE_FORMAT('2014-11-24 10:48:09','%Y-%m-%d %H:%i:%s')

this for datetime format in mysql using DATE_FORMAT(date,format).

Answer 5

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

it helps , you can convert the values as DATE before comparing.

Answer 6

You can cast the DATETIME field into DATE as:

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

This is very much efficient.

Answer 7

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.