MySQL: Using BETWEEN in a SQL query

Posted November 6th, 2009 in MySql

I mentioned in my Using + to merge arrays with PHP post the other day that I've recently started a new job and one of the interesting things about working with other people's code is the new stuff you learn. So I was surprised to discover that in 11 years of working with SQL databases that I've never come across the BETWEEN operator before. While this post is titled "MySQL" the SQL used here should work for most database systems.

SELECT BETWEEN

If an example database table had a column name "date_published" which recorded the date a blog post was published, and we wanted to find all the posts that had been published in October 2009, we could do this:

SELECT *
FROM mytable
WHERE date_published >= '2009-10-01'
AND date_published <= '2009-10-30'

Using BETWEEN, the SQL query can be simplified like so:

SELECT *
FROM mytable
WHERE date_published BETWEEN '2009-10-01' AND '2009-10-30'

It will find all the blog posts between and including October 1st 2009 and October 30th 2009. The BETWEEN operator can be used with text and numeric data types as well as dates.