4

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

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
Kold
  • 65
  • 1
  • 6
  • I need to link those months with student fees ... their fees between the start of session and end of session – Kold Mar 01 '12 at 06:10
  • Does `select DISTINCT month(date) from ` work for you?
     – ScottJShea Mar 01 '12 at 06:10
  • you need create a procedure i guess...refer here http://stackoverflow.com/questions/510012/get-a-list-of-dates-between-two-dates.. – chinna_82 Mar 01 '12 at 06:13
  • yes the fee table has a start date and end date but as the fee can be change during the middle of the session.. the month and date might not be useful – Kold Mar 01 '12 at 06:15

1 Answers1

10

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.

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • in the second line you should replace 'day' with 'month', than you can remove the last line 'group by Year(...' – user1027167 Mar 01 '12 at 07:40