179

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

Improve this question
1
  • 2
    Nope. I my MySQL installation (version?) BETWEEN is inclusive for both values. I have MySQL Server 5.7 on Windows 10. Commented Sep 18, 2017 at 6:10

11 Answers 11

Reset to default
336

From the MySQL-manual:

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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.
@jemminger may be because the answer is from archrival-postgres guy :P
In short between is inclusive...that is why this answer rocks.
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.
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.
216

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'
Improve this answer

8 Comments

Instead of CAST(dob AS DATE) you can use the more succinct DATE(dob).
While this works, you'll get better performance by using >= and < instead of between.
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 Please add an answer with this solution. CAST isn't as nearly efficient.
@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.
|
137

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.

Improve this answer

2 Comments

This really explains what is going on and answers the question.
After all those years this answer is still the best. Thanks a lot.
34 
select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'
Improve this answer

6 Comments

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.
rahmanisback from MySQL Documentation I can confirm the last second WILL be included since BETWEEN is in both-ways inclusive. refer to dev.mysql.com/doc/refman/5.5/en/…
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.
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?
-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.
|
9

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.

Improve this answer

Comments

7

Hi this query works for me,

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

1 Comment

Do you have a date field or datetime field?
5 
select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'

Surprisingly such conversions are solutions to many problems in MySQL.

Improve this answer

1 Comment

Surprisingly, this is exactly what the accepted answer (and several others) said... 2 years before you did.
3

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

Improve this answer

Comments

1

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!

Improve this answer

Comments

0

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

Improve this answer

1 Comment

This will incorrectly include zero time on 1st February.... Which is why BETWEEN should be ignored; but >= and < should be used instead.
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%".

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.