Creating a list of month names between two dates in MySQL

Question

How can I create a list of all month names, for example, January, February, etc. between two dates. for example, from 2012-02-01 to 2013-03-29 with MySQL .. whereas February will be generated twice, one for 2012 and another 2013

Answer 1

I think this is what you are looking for:

select MonthName(aDate) from ( select @maxDate - interval (aa + (10 * ba) + (100 * ca)) 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 will find this post and find it a little harder to understand. I add a short explanation:

This is a dynamically (and a bit ugly) solution for creating a date range that does not require creating a table, and is based on the following query, which generates a sufficient number of records for most applications (10,000 records):

 select aDate from ( select @maxDate - interval (a.a+(10*ba)+(100*ca)+(1000*da)) 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 number of dynamically generated results (10,000 days means more than 27 years of records, each of which represents one day) by deleting the tables (d, c, b and a in this order) and also removing them from the top formulas. Setting the variables @minDate and @maxDate will allow you to specify dates between the result filters.