MySQL where clause with DATE_FORMAT

Issue

I have a MySQL table with a date column.

I am trying select all the rows between some particular date range as,

select * from myTable mt 
    where DATE_FORMAT(mt.DateCol, '%y-%m-%b') between '01/03/2015' and '09/03/2015'

where 01/03/2015 and 09/03/2015 are from and to date range selected by user which is in dd/mm/yyyy format and my datecolumn is in yyyy-mm-dd format. So how can I directly select the rows using DATE_FORMAT function. The above query gives zero result.

Solution

And why would you convert a date to a string for this? Just do:

select *
from myTable mt
where mt.DateCol between '2015-03-01' and '2015-03-09';

If you like, you can add date() around the constants to emphasize their types:

select *
from myTable mt
where mt.DateCol between date('2015-03-01') and date('2015-03-09');

Answered By – Gordon Linoff

Answer Checked By – Robin (AngularFixing Admin)