MySQL "between" clause not inclusive?

Question

If I run a query with a between clause, it seems to exclude the ending value.
For example:

select * from person where dob between '2011-01-01' and '2011-01-31'

This gets all results with dob from '2011-01-01' till '2011-01-30'; skipping records where dob is '2011-01-31'. Can anyone explain why this query behaves this way, and how I could modify it to include records where dob is '2011-01-31'? (without adding 1 to the ending date because its been selected by the users.)

Nope. I my MySQL installation (version?) `BETWEEN` is inclusive for both values. I have `MySQL Server 5.7` on Windows 10. — Green, Sep 18 '17 at 06:10

score 325 · Answer 1 · answered Feb 22 '11 at 16:30

325

From the MySQL-manual:

This is equivalent to the expression (min <= expr AND expr <= max)

answered Feb 22 '11 at 16:30

Frank Heikens

117,544
24
142
135

3

The manual linked in this answer shows that a cast is preferred when comparing DATE and DATETIME objects. So I guess @tiagoinu has the most complete answer in the strictest sense, but both are spot on. – Kingsolmn Mar 05 '13 at 13:39
@jemminger may be because the answer is from *archrival*-postgres guy :P – nawfal Jun 17 '13 at 13:51
39

In short **between is inclusive**...that is why this answer rocks. – Rafael Mar 08 '15 at 06:29
11

Old comment, but I wanted to tie this to the specific query. "BETWEEN" is inclusive, but dates with no time specified pad to 00:00:00. Comparing on a date range will therefore lose the last day. Either call DATE(dob) or specify the end of the day. – wintermute92 Sep 20 '16 at 19:46
they say practice is gold, from my use case it is not inclusive at all, I wonder why this happens with me. I tried and sometimes it works sometimes not. using it on TIME data field. – Jeffery ThaGintoki Aug 20 '18 at 23:28

score 210 · Accepted Answer · edited Dec 30 '13 at 16:24

210

The field dob probably has a time component.

To truncate it out:

select * from person 
where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'

edited Dec 30 '13 at 16:24

Eric Leschinski

146,994
96
417
335

answered Feb 22 '11 at 16:40

tiago2014

3,392
1
21
28

67

Instead of `CAST(dob AS DATE)` you can use the more succinct `DATE(dob)`. – jkndrkn Sep 30 '11 at 15:36
13

While this works, you'll get better performance by using `>=` and `<` instead of `between`. – David Harkness Mar 20 '12 at 00:42
124

You will get better performance by using `dob BETWEEN '2011-01-01 00:00:00' AND '2011-01-31 23:59:59`. This is because `DATE(dob)` has to calculate a value for each row and cannot use any indexes on that field. – joshuahedlund Jul 31 '12 at 13:39
2

@joshuahedlund Please add an answer with this solution. CAST isn't as nearly efficient. – doc_id May 27 '15 at 01:04
4

@joshuahedlund That works until you have data with times `t > 23:59:59 and t < 24:00:00`. Why deal with poorly specified `BETWEEN` at all? Rather follow David's advice and use: `WHERE dob >= '2011-01-01' AND dob < '2011-02-01'`. Best performance, and it works every time. – Disillusioned Feb 27 '17 at 09:13
@DavidHarkness, Why do you think so? Elaborate on this please. – Green Sep 18 '17 at 06:11
@Green to avoid the cost of conversion and gain the speed of an index on the field as joshuahedlund and Craig Young clarified. It's possible that MySQL optimizes queries like this to be equivalent. – David Harkness Sep 19 '17 at 11:49
@joshuahedlund I find that when i use this `SELECT * FROM sales WHERE created >= '2020-01-09 00:00:00' AND created <= '2020-01-09 11:59:59' AND store_id=3` is doesn't select data but when i use this `SELECT * FROM sales WHERE DATE(created) >= '2020-01-09 00:00:00' AND DATE(created) <= '2020-01-09 11:59:59' AND store_id=3` it does return data. the date field is datetime, do you know why that happens? – Smith Jan 15 '20 at 20:25

score 126 · Answer 3 · answered Feb 22 '11 at 16:35

126

The problem is that 2011-01-31 really is 2011-01-31 00:00:00. That is the beginning of the day. Everything during the day is not included.

answered Feb 22 '11 at 16:35

Daniel Hilgarth

171,043
40
335
443

29

This really explains what is going on and answers the question. – Ivan P Jan 02 '14 at 22:26
4

After all those years this answer is still the best. Thanks a lot. – Strabek Feb 25 '17 at 11:09

score 33 · Answer 4 · answered Feb 22 '11 at 16:30

33

select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'

answered Feb 22 '11 at 16:30

Gaurav

28,447
8
50
80

Daniel Hilgarth's answer explains the problem, Guarav provides the quick and easy solution. – Geoff Kendall Nov 07 '14 at 11:53
1

I *think* it worth noting that, this will not include dates at `2011-01-31 23:59:59` but will include those up to `2011-01-31 23:59:58` *the last second of the day is not included* It could be minor but someone will benefit from. – doc_id May 27 '15 at 01:13
1

rahmanisback from MySQL Documentation I can confirm the last second WILL be included since BETWEEN is in both-ways inclusive. refer to http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#operator_between – Felype Nov 25 '15 at 13:17
1

Yes, @Felype you're right. I checked this myself in mysql database. It includes also the `23:59:59` in the result. So its both ways inclusive. – Lucky Feb 29 '16 at 11:37
2

If the `dob` column is a timestamp with sub-second precision, then won't `BETWEEN` still miss events within the final second of the day unless '2011-02-01 00:00:00' is used instead? – nitrogen Jul 21 '16 at 22:08
2

-1. Won't include `2011-01-31 23:59:59.003`. @nitrogen using `2011-02-01 000:00:00` will _incorrectly_ include zero time on 1st February.... Which is why `>=` and `<` should be used instead. – Disillusioned Feb 27 '17 at 09:19

score 8 · Answer 5 · answered Feb 22 '11 at 16:34

Is the field you are referencing in your query a Date type or a DateTime type?

A common cause of the behavior you describe is when you use a DateTime type where you really should be using a Date type. That is, unless you really need to know what time someone was born, just use the Date type.

The reason the final day is not being included in your results is the way that the query is assuming the time portion of the dates that you did not specify in your query.

That is: Your query is being interpreted as up to Midnight between 2011-01-30 and 2011-01-31, but the data may have a value sometime later in the day on 2011-01-31.

Suggestion: Change the field to the Date type if it is a DateTime type.

score 7 · Answer 6 · edited Feb 29 '16 at 11:40

7

Hi this query works for me,

select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'

edited Feb 29 '16 at 11:40

Lucky

16,787
19
117
151

answered Dec 09 '13 at 23:18

infinito84

1,971
17
8

Do you have a date field or datetime field? – Duke Aug 11 '22 at 13:40

score 4 · Answer 7 · answered Aug 14 '13 at 12:22

4

select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'

Surprisingly such conversions are solutions to many problems in MySQL.

answered Aug 14 '13 at 12:22

betty.88

157
5

11

Surprisingly, this is exactly what the accepted answer (and several others) said... 2 years before you did. – Chris Baker Sep 19 '14 at 22:47

score 3 · Answer 8 · answered Oct 29 '19 at 08:25

In MySql between the values are inclusive therefore when you give try to get between '2011-01-01' and '2011-01-31'

it will include from 2011-01-01 00:00:00 upto 2011-01-31 00:00:00 therefore nothing actually in 2011-01-31 since its time should go from 2011-01-31 00:00:00 ~ 2011-01-31 23:59:59

For the upper bound you can change to 2011-02-01 then it will get all data upto 2011-01-31 23:59:59

score 1 · Answer 9 · answered Jan 04 '19 at 17:37

You can run the query as:

select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'

like others pointed out, if your dates are hardcoded.

On the other hand, if the date is in another table, you can add a day and subtract a second (if the dates are saved without the second/time), like:

select * from person JOIN some_table ... where dob between some_table.initial_date and (some_table.final_date + INTERVAL 1 DAY - INTERVAL 1 SECOND)

Avoid doing casts on the dob fiels (like in the accepted answer), because that can cause huge performance problems (like not being able to use an index in the dob field, assuming there is one). The execution plan may change from using index condition to using where if you make something like DATE(dob) or CAST(dob AS DATE), so be careful!

score 0 · Answer 10 · answered Feb 22 '11 at 16:26

0

Set the upper date to date + 1 day, so in your case, set it to 2011-02-01.

answered Feb 22 '11 at 16:26

Rafal

2,576
2
18
13

1

This will _incorrectly_ include zero time on 1st February.... Which is why `BETWEEN` should be ignored; but `>=` and `<` should be used instead. – Disillusioned Feb 27 '17 at 09:23

score 0 · Answer 11 · answered Feb 20 '22 at 16:28

0

select * from person where dob between '2011-01-01' and '2011-01-31' or dob like' 2011-01-31%'

Just add or <<column>> like "date%".

answered Feb 20 '22 at 16:28

R4zonsg

1

MySQL "between" clause not inclusive?

11 Answers11

Linked