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.)
select * from person where DATE(dob) between '2011-01-01' and '2011-01-31'
Surprisingly such conversions are solutions to many problems in MySQL.
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.
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
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.
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!
Hi this query works for me,
select * from person where dob between '2011-01-01' and '2011-01-31 23:59:59'
Set the upper date to date + 1 day, so in your case, set it to 2011-02-01.
From the MySQL-manual:
This is equivalent to the expression (min <= expr AND expr <= max)
select * from person where dob between '2011-01-01 00:00:00' and '2011-01-31 23:59:59'
Source: Stackoverflow.com