lcp2000 lcp2000 - 4 years ago 222
SQL Question

MySQL: Unable to query between two dates if one date is in 2015 and other is in 2016

Been using the below query for the last year but now that we are crossing into a new year, it seems to have stopped working (at least for this week). Don't understand why...

SELECT * FROM tbl WHERE userID = 67 AND date BETWEEN '12/27/2015' AND '01/02/2016'


Any help would be appreciated

Answer Source

If date is a column of type date, you should write your dates with this format:

SELECT * FROM tbl WHERE userID = 67 AND date BETWEEN '2015-12-27' AND '2016-01-02'

if date is a varchar instead, and the format is correct, you could try with this:

WHERE str_to_date(date, '%m/%d/%Y') BETWEEN '2015-12-27' AND '2016-01-02'

but I would suggest you not to store dates as strings.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download