user1721546 user1721546 - 5 months ago 9
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(

a
.
refresh_time
) = '2016-06-11'

Answer

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

SAMPLE

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

OR

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.