user1721546 user1721546 -5 years ago 118
SQL Question

Filter on date in MySQL

I am trying to convert a date time column to date and get all the rows in a particular day . But when try it. The query returns 0 rows .What is wrong with this code
where date(

) = '2016-06-11'

Answer Source

Do not!!! use a function on a field in WHERE clause like the Answer from @scaisEdge. If you do that like this sample MySQL must read ALL rows in the table convert it with your function and can then compare it. And it never can use a index

If is better and quicker to use like this


WHERE `a`.`refresh_time` >=  '2016-06-11 00:00:00' AND `a`.`refresh_time` <=  '2016-06-11 23:59:59';


WHERE `a`.`refresh_time` BETWEEN  '2016-06-11 00:00:00' AND '2016-06-11 23:59:59';

So MySQL can direct use a INDEX to find the correct ROWs

You can test it with EXPLAIN before your SELECT.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download