CODEWITHSUNDEEP
mysqlformsdatereportbetween
Asif Hïñgörä
Asif Hïñgörä

Reputation: 11

SQL Between query not work properly

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'

Upvotes: 1

Views: 751

Answers (2)

Mureinik
Mureinik

Reputation: 311998

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')

Upvotes: 0

Manoj Sharma
Manoj Sharma

Reputation: 1465

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.

Upvotes: 1

Related Questions