coco coco - 5 months ago 11
MySQL Question

SQL "BETWEEN" request not acting as I want

I'm having some issues with my SQL request.

Here's the request :

SELECT SUBSTR(`Date`, 1, 11) AS `format_date`
FROM table
HAVING `format_date` BETWEEN '07/06/2016' AND '16/06/2016'


When I run the request I get not wanted results like "07/11/2014". After doing some tests it looks like the request is only taking the day in consideration but I can't really figure out why. Any ideas ?

Answer

07/11/2014is between the two given strings. You are comparing strings, not dates, but you are getting exactly what you ask for.

This is what you compare:

"07/0" < "07/1" < "16/0"

Try comparing actual dates, or format your string so that you can use them (YYYY/MM/DD).

This question (and its accepted answer) should help you convert your strings to useable dates: how-to-convert-a-string-to-date-in-mysql

You can then copmpare real dates with each other.