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

User · Answer

From the MySQL-manual      This is equivalent to the expression    min  lt   expr AND expr  lt   max

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Hi this query works for me   select   from person where dob between  2011-01-01  and  2011-01-31 23 59 59

User · Answer

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

User · Answer

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

User · Answer

select   from person where DATE dob  between  2011-01-01  and  2011-01-31    Surprisingly such conversions are solutions to many problems in MySQL

User · Answer

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

[sql] MySQL "between" clause not inclusive?

Examples related to sql

Examples related to mysql

Examples related to between