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)