How to check if a date is between two dates mysql?

Question

I'm querying dates from a mysql database. When I try to select only the dates between a certain from and to date, I'm not getting the expected results back.

My query:

select * from tbl_billing where date BETWEEN '09-02-2017' and '10-02-2017'

My output:

09-02-2017
10-02-2017
10-01-2017
09-01-2017
09-01-2017

My desired result:

09-02-2017
10-02-2017

Answer 1

You can use canonical sql date format

select * from tbl_billing where date BETWEEN '2017-02-09' and '2017-02-10'

or convert properly using str_to_date

select * from tbl_billing where date BETWEEN str_to_date('09-02-2017', '%d-%m-%Y')
            and  str_to_date('10-02-2017', '%d-%m-%Y')

but if also your date is a varchar then you must convert this column too

select * from tbl_billing where str_to_date(date,'%d-%m-%Y')  BETWEEN str_to_date('09-02-2017', '%d-%m-%Y')
            and  str_to_date('10-02-2017', '%d-%m-%Y')