MySQL "between" clause not inclusive?

sql mysql between
ASD picture ASD · Feb 22, 2011 · Viewed 123.9k times · Source

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

Answer

Frank Heikens picture Frank Heikens · Feb 22, 2011

From the MySQL-manual:

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

Related questions

generate days from date range

I would like to run a query like select ... as days where `date` is between '2010-01-20' and '2010-01-24' And return data like: days ---------- 2010-01-20 2010-01-21 2010-01-22 2010-01-23 2010-01-24

Read More
Mysql: Selecting values between two columns

I'm trying to select a value between 2 columns. Here is my dataset id from to price 1 0.00 2.00 2.50 2 2.00 3.00 3.00 3 3.00 4.00 4.50 My goal, if I have a value of 2 is to select the line with the ID 1 (between from and to). So here is the …

Read More
SQL NOT BETWEEN query

Thanks in advance for any advice or tips! I have a booking table in a mysql database, table1. It contains a start date and a finish date. I have another table, table2 which contains the information I need to get …

Read More