How to select rows within some date range

Question

I want to select rows that falls between some date range, I tried the following query but it didn't work.

SELECT * FROM tbl WHERE DATE_FORMAT(date_col, '%2011-%c-%e') BETWEEN '2011-11-28' AND '2011-12-5' 

It doesn't seem like the BETWEEN keyword works on date. Please how do I get the results? Thanks

Answer 1

You don't need to use DATE_FORMAT if you want to compare dates.

SELECT * 
FROM tbl 
WHERE DATE(date_col) BETWEEN '2011-11-28' AND '2011-12-05'

Your code compares strings, assuming you use DATE_FORMAT(date_col, '%Y-%c-%e')