Mysql select where date = now if Y-m-d H:i:s

Question

How to SELECT rows of a particular day if date format is Y-m-d H:i:s?

I.e. I'd like to select rows like:

WHERE DATE(datet) = '2013-08-07'

but values are: 2013-08-07 11:23:45

possible duplicate of [How to cast DATETIME as a DATE in mysql?](http://stackoverflow.com/questions/1468807/how-to-cast-datetime-as-a-date-in-mysql) — naththedeveloper, Aug 08 '13 at 09:23
exactly like that: MySQL can convert DATETIME to DATE via the DATE() function — cypherabe, Aug 08 '13 at 09:24

score 1 · Answer 1 · answered Aug 08 '13 at 10:52

You have the solution yourself:

SELECT * FROM tbl WHERE DATE(datet) = '2013-08-07'

Assuming that datet is a DATETIME column this is the most efficient way of doing since then MySQL will be able to use a potential index on datet to process the WHERE clause.

Depending your version of MySQL WHERE datet LIKE '2013-08-07%' might do the job by using the index too (since the leftmost part of the pattern is constant). But this is less elegant, I think.

EDIT: As a matter of fact, by testing it appears that will work efficiently with VARCHAR/CHAR columns too. See http://sqlfiddle.com/#!8/1c2da/1 (click on "View Execution Plan" to see how the index was used).

score 0 · Answer 2 · answered Aug 08 '13 at 09:26

0

Use like this

 SELECT DATE('2013-08-07 11:23:45');

It will return

    -> '2013-08-07'

You can use

WHERE DATE(datet) = DATE('2013-08-07 11:23:45')

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date

answered Aug 08 '13 at 09:26

Bindiya Patoliya

2,726
1
16
15

score 0 · Answer 3 · answered Aug 08 '13 at 09:35

0

You can do this following query try with the following query:

SELECT * FROM `YOUR TABLE` WHERE DATE(`YOUR FIELD`) = DATE("2013-08-07");

OR

SELECT * FROM `YOUR TABLE` WHERE `YOUR FIELD` LIKE "%2013-08-07%";

answered Aug 08 '13 at 09:35

Dhaval Bharadva

3,053
2
24
35

Mysql select where date = now if Y-m-d H:i:s

3 Answers3