SQL "BETWEEN" request not acting as I want

Question

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 ?

Looks like a valid text comparison to me, you might want to compare the day, month and year of the two dates manually to ensure correct date semantic. — Smutje, Jun 13 '16 at 09:26
Change the data type to `date`. THAT is the only real solution — juergen d, Jun 13 '16 at 09:27
The reason why its varchar is because the datas come from a Excel file, I guess the default is always varchar. However, when I try to convert into Date it doesn't work. The format of my dates are : dd/mm/yyyy hh:mm:ss (I'm french) — coco, Jun 13 '16 at 09:31
@Smutje Sorry to say, but I don't think that comment's very helpful. — Strawberry, Jun 13 '16 at 09:34
I'm Australian dd/mm/yyyy hh:mm:ss is my "normal" aussi, BUT that format a poor choice when doing comparisons or trying to sort the data. YYYY-MM-DD hh:mm:ss would be better. Truly you do need to store dates as dates, not text. Using between for date ranges is `NOT` best practice either. MySQL has moved to sub-second time precision in recent versions so it's time to learn how to use >= and < for more accurate date range selection. — Paul Maxwell, Jun 14 '16 at 23:58

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

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.

edited May 23 '17 at 12:31

Community

1
1

answered Jun 13 '16 at 09:40

oerkelens

5,053
1
22
29

Alright, I see whats wrong. But I'm not sure what method to use in order to change my dd/mm/yyyy to yyyy/mm/dd. Could you help ? – coco Jun 13 '16 at 10:02
@coco I added a link to the relevant question on SO to convert your strings to dates. – oerkelens Jun 13 '16 at 10:10

score 1 · Answer 2 · answered Jun 13 '16 at 10:04

The reason what you get wrong result is clear already, you can try following;)

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

And I think you should change HAVING to WHERE.

score 0 · Answer 3 · answered Jun 13 '16 at 09:36

0

Your problem can be summed up in the following expression...

SELECT '8' BETWEEN '7' AND '77';
+--------------------------+
| '8' BETWEEN '7' AND '77' |
+--------------------------+
|                        0 |
+--------------------------+

answered Jun 13 '16 at 09:36

Strawberry

33,750
13
40
57

score 0 · Answer 4 · answered Jun 13 '16 at 10:05

There are 3 problems to address.

The simplest is that you have wrongly used HAVING instead of WHERE.
You have converted dates into strings in the form dd/mm/yyyy. The problem here is that text (strings) do NOT behave like numbers e.g. in text 1 and 10 are sorted before 2 or 3. Due to this you are asking for TEXT between STRINGS THAT LOOK LIKE DATES but it will not behave thay way because it requires handling dates as numeric.
Between is a very poor way to handle date ranges. A far more reliable way is to use a combination of >= and < as seen below.

Example

SELECT SUBSTR(Date, 1, 11) AS `format_date` 
FROM table 
WHERE `date` >= '2016-06-06'' AND `date` < '2016-06-17'

SQL "BETWEEN" request not acting as I want

4 Answers4