SQL Between query not work properly

Question

SELECT * FROM `incomes` WHERE incomeDate BETWEEN '28-12-2016' and '31-01-2017'

This query not work but When i do below query it show result

SELECT * FROM `incomes` WHERE incomeDate BETWEEN '28-12-2016' and '31-12-2016'

Answer 1

Please change date format, MYSQL Support 'YYYY-MM-DD' date formates in comparion. Please use below query.

SELECT * FROM incomes WHERE incomeDate BETWEEN '2016-12-28' and '2017-01-31'.

Hope this will help you.

Answer 2

You are comparing a date column to (two) string literals. What happens here is the date is converted to a string and compares lexicographically. Instead, you should explicitly convert the strings to dates, and thus compare them by date order:

SELECT * 
FROM   incomes 
WHERE  incomeDate BETWEEN STR_TO_DATE('28-12-2016', '%d-%m-%Y') AND 
                          STR_TO_DATE('31-01-2017', '%d-%m-%Y')