Mr. Developer Mr. Developer - 4 months ago 21
MySQL Question

How to get month name by month number inside the query using mysqli?

I'm trying to get the count of added records by month. Following query is working fine

SELECT COUNT(*) AS count_by_month, MONTH(date_added) AS in_month
FROM `promotion`
WHERE YEAR(date_added) = '2016'
GROUP BY MONTH(date_added)


But above query is returning month number in the result, I wanted to get the month name like
Jan
,
Feb
instead of number. I tried following trick but it does not working.

SELECT COUNT(*) AS count_by_month,
MONTH(date_added) AS in_month, MONTHNAME(STR_TO_DATE(in_month, '%m'));
FROM `ws_promotion`
WHERE YEAR(date_added) = '2016'
GROUP BY MONTH(date_added)


Can anyone guide me how can fix the issue, i would like to appreciate if someone guide me regarding this. Thank You.

Answer

Try this:

SELECT    
    COUNT(*) AS count_by_month, 
    MONTH(date_added) AS in_month, 
    MONTHNAME(date_added)
FROM      `ws_promotion` 
WHERE     YEAR(date_added) = '2016' 
GROUP BY  MONTH(date_added)

Note: MONTHNAME(date) function expects date as argument.

Example:

mysql> SELECT MONTHNAME('2009-05-18');
+-------------------------+
| MONTHNAME('2009-05-18') |
+-------------------------+
| May                     | 
+-------------------------+
1 row in set (0.02 sec)

More: If you want to get month name from numbers then you can use STR_TO_DATE() to convert the number to a date, and then back with MONTHNAME()

SELECT MONTHNAME(STR_TO_DATE(7, '%m'));

+---------------------------------+
| MONTHNAME(STR_TO_DATE(7, '%m')) |
+---------------------------------+
| July                            |
+---------------------------------+
Comments