MySQL Filter Records Set Using DD-MM-YYYY Date Format

Question

I'm a bit confused on how to filter records by date formats.

I have date column of date(yyyy-mm-dd) data type in date table.

Ex:

date
-----
2017-01-29
2017-01-30

I'm want to change the format (dd-mm-yyyy). I'm using this code

SELECT 
   DATE_FORMAT(`date`, '%d-%m-%Y') as date
FROM
    dim_date;

date
-----
29-01-2017
30-01-2017

I want to filter the records with the(dd-mm-yyyy) format. So I tried this code.

SELECT 
   DATE_FORMAT(`date`, '%d-%m-%Y') date
FROM
    dim_date
WHERE DATE_FORMAT(`date`, '%d-%m-%Y') BETWEEN '20-04-2015' AND '06-09-2017';

Results Nothing

But if I try to filter with the original format (yyyy-mm-dd) It Works.

SELECT DATE_FORMAT(`date`, '%d-%m-%Y') date
FROM dim_date
WHERE DATE_FORMAT(`date`, '%Y-%m-%d') BETWEEN '2015-04-20' AND '2017-01-07';

Why is this weird behavior in Mysql? Am I missing something here?

I also tried with this format DATE_FORMAT(date, '%d-%c-%Y') , DATE_FORMAT(date, '%d-%l-%Y') & DATE_FORMAT(date, '%e-%l-%Y') No happy face, Please let me known.

Thanks

Max

i don't see difference between the two query .. .. please explain better — ScaisEdge
Date_format will give you a string. And if you compare your values as a string, there is no character that is >=2 and <=0 (which is what between '2' and '0' will be looking for). Why do you want to use it that way? You can just convert your input to a valid date and compare valid dates. It will also allow you to use indexes. And, well, it will actually filter as you want it to work. — Solarflare
@scaisEdge I have updated the query. please check now. Sorry, I am confused with the date formats for now. — MAX

ScaisEdge ScaisEdge · Accepted Answer · 2017-09-26T16:49:12

with your query you convert the date in string and then you are comparing values using between in wrong order ('20' > '06' ) so don't work. Between require first the min value and second the max value.

If you are working with date you sould convert the string date
so the where between work correcly and avoid the string behavior is filter

SELECT date 
FROM dim_date
WHERE date   BETWEEN str_to_dat('20-04-2015', '%d-%m-%Y') 
            AND str_to_date('06-09-2017', '%d-%,-%Y');

MySQL Filter Records Set Using DD-MM-YYYY Date Format

1 Answers