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

Answer 1

Your SELECT statement looks fine - except for the formatting and the missing semi-colon (;). In order to be more professional with your programming, you really should apply more conventional capitalization and so forth.

For example:

select * from person where dob between '2011-01-01' and '2011-01-31'

... should be written as

SELECT * FROM person WHERE dob Between '2011-01-01' And '2011-01-31';

But otherwise, what you've coded is right. The Between + And is inclusive. Of course, if don't have any records in the database falling on 2011-01-31, then the query won't return data for that day.

If, for some strange reason your code still won't work, you can try

SELECT * FROM person

WHERE dob >= '2011-01-01'

And dob <= '2011-01-31'

You could also try

SELECT * FROM person

WHERE dob Between CAST('2011-01-01' AS DATE) And CAST('2011-01-31' AS DATE);

And finally

SELECT * FROM person

WHERE dob >= CAST('2011-01-01' AS DATE)

And dob <= CAST('2011-01-31' AS DATE);