MySql : How to Display the Month Names between a given range column wise

Displaying Month Names Column wise in a given range

SELECT MONTHNAME(STR_TO_DATE(6, '%m')) Month;
OUTPUT

+---------------------------------+
| Month                                   |
+---------------------------------+
| June                            |
+---------------------------------+


Now How to Display all months from JAN to DEC.

select STR_TO_DATE(CONCAT('01-',date_format(STR_TO_DATE(a1.months,'%m'),'%b'),'-11'),'%d-%b-%y') as date
from (select 1 months union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 months union select 8 union select 9 union select 10 union select 11 union select 12) a1


OUTPUT
+------------+
| date            |
+------------+
| 2011-01-01 |
| 2011-02-01 |
| 2011-03-01 |
| 2011-04-01 |
| 2011-05-01 |
| 2011-06-01 |
| 2011-07-01 |
| 2011-08-01 |
| 2011-09-01 |
| 2011-10-01 |
| 2011-11-01 |
| 2011-12-01 |
+------------+
Here I concatinated '2011' Year so we are getting all 2011 months. I am displaying first date of each month. Suppose if you want only Month and Year Names as Output.
select MONTHNAME(STR_TO_DATE(a1.months, '%m')) as date
from (select 1 months union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 months union select 8 union select 9 union select 10 union select 11 union select 12) a1

Output
+-----------+
| date      |
+-----------+
| January   |
| February  |
| March     |
| April     |
| May       |
| June      |
| July      |
| August    |
| September |
| October   |
| November  |
| December  |
+-----------+

Now the Original question How to get the dates between given date range for example Oct-2011 to May-2012.

select * from (
select STR_TO_DATE(CONCAT('01-',date_format(STR_TO_DATE(a1.months,'%m'),'%b'),'-11'),'%d-%b-%y') as date
from (select 1 months union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 months union select 8 union select 9 union select 10 union select 11 union select 12) a1 
UNION
select STR_TO_DATE(CONCAT('01-',date_format(STR_TO_DATE(a2.months,'%m'),'%b'),'-12'),'%d-%b-%y') as date
from (select 1 months union select 2 union select 3 union select 4 union select 5 union select 6 union
select 7 months union select 8 union select 9 union select 10 union select 11 union select 12) a2
) b where date between STR_TO_DATE('01-Oct-11','%d-%b-%y') and STR_TO_DATE('01-may-12','%d-%b-%y');


Output
+------------+
| date       |
+------------+
| 2011-10-01 |
| 2011-11-01 |
| 2011-12-01 |
| 2012-01-01 |
| 2012-02-01 |
| 2012-03-01 |
| 2012-04-01 |
| 2012-05-01 |
+------------+






No comments: