SQL - Query a Whole Month Based on One Date

Often with date based data, such as events listings, you may need to extract all the records that match a particular calendar month. Here all you need from the user is a month and year. Then you can let SQL do the rest, for example:

SELECT filmName, filmReleaseDate FROM films
WHERE filmReleaseDate BETWEEN '2013-06-1'
AND DATE_ADD('2013-06-1', INTERVAL 1 MONTH)

The explanation bit: the magic here is done by DATE_ADD and INTERVAL 1 MONTH.

DATE_ADD takes two parameters, the date we are starting from and then the interval in time to the new date.

DATE_ADD(startingDate, Interval-to-add)

The interval can be a range of values such as DAY, WEEK, MONTH and YEAR. So in our example we add one month to the query with:

DATE_ADD('2013-06-1', INTERVAL 1 MONTH)