Creating a list of month names between two dates in MySQL

Question

How can i create a list of all the month names e.g January, February etc.. between two dates. e.g 2012-02-01 to 2013-03-29 with MySQL .. whereas February will be generated twice, one for the year 2012 and the other 2013

Answer 1

I guess this is what you're looking for:

select MonthName(aDate) from (
  select @maxDate - interval (a.a + (10 * b.a) + (100 * c.a)) month as aDate from
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) a,
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) b,
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) c,
  (select @minDate := '2012-02-01', @maxDate := '2013-03-29') d
) e
where aDate between @minDate and @maxDate

---

Just in case someone finds this post and finds it a bit harder to understand it I'm a adding a short explanation:

This is a dynamically (and a bit ugly) solution to creating a date range that does not require creating a table and is based on the following query which generates enough records for most applications (10000 records):

select aDate from (
  select @maxDate - interval (a.a+(10*b.a)+(100*c.a)+(1000*d.a)) day aDate from
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) a, /*10 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) b, /*100 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) c, /*1000 day range*/
  (select 0 as a union all select 1 union all select 2 union all select 3
   union all select 4 union all select 5 union all select 6 union all
   select 7 union all select 8 union all select 9) d, /*10000 day range*/
  (select @minDate := '2001-01-01', @maxDate := '2002-02-02') e
) f
where aDate between @minDate and @maxDate

Depending on the length of the date range you can reduce the amount of dynamically generated results (10000 days means over 27 years of records each representing one day) by removing tables (d, c, b and a in that order) and also removing them from the upper formula. Setting the @minDate and @maxDate variables will allow you to specify the dates between you want to filter the results.