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 it been selected by the users.)

Answer 1

The field dob probably has the time component.

To truncate it out, you can use the below code:

select * from person 

where CAST(dob AS DATE) between '2011-01-01' and '2011-01-31'