127

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.

Improve this question

6 Answers 6

Reset to default
215

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

The first answer is so slow, because it has to format each datetime into a string before comparison. Your is better, because it directly compares only the date part from the field, but it still cannot use index (tested on mysql 5.1)
If performance is an issue it might be worth considering storing the date and time part separately, so that an INDEX can be placed on the date part.
This query will not be able to use index if startTime is indexed.
But the this answer will result in slow queries for big tables DATE(startTime) neglects index of startTime is its set.
61

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.

Improve this answer

3 Comments

David's version is not good, it cannot use indexes. To use an index, you have to filter on the column directly, not on a result of a function (date(), year(), datediff() or any similar)
I was looking for how mysql converts the date to datetime , thanks for clearing it adds 00:00:00 . now my results makes sense
This is correct fast solution compared to other answers which cannot use index if available
27 
SELECT * FROM `calendar` WHERE DATE_FORMAT(startTime, "%Y-%m-%d") = '2010-04-29'"

OR 

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

5 Comments

This is a terrible query. Avoid this at all costs. If there is an index on startTime it cannot be used by this query, since it's applying a function to the column rather than using it directly. That means any query like this will require a full table scan. On a large table this will mean an extremely slow query.
I agree, his answer is wrong - the correct answer was posted by David below.
Please, use the answer from David, because it's way more fast. This one is valid, but not good for the speed...
@CarlosAlbertoMartínezGadea This version is slow because it has to format each value to string before comparison... David's version does not need it, but still cannot use indexes, so is not optimal. See answer from XL_ for a version which can use index. Unfortunately there is no better way to do it properly in mysql
I downvoted this answer because the query cannot use an index, while @XL_'s answer does.
3 
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).

Improve this answer

1 Comment

Better add some explanation
0

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.

Improve this answer

1 Comment

No it is not efficient too. this will not be able to use index if startTime is indexed
-6 
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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.