SQL Statement Convert it to 1 to Jan, 2 to Feb and so on

Question

As you seen the image below, the month is now 1 and 2. How can I convert it to 1 to Jan, 2 to Feb and so on. Below are my sql.

Help will be appreciate. Thanks in advance! :)

SELECT DISTINCT fbmenuname, 
                Sum(quantity)                        AS ordercount, 
                Month(Str_to_date(date, '%d/%m/%Y')) AS month 
FROM   fb.fborders 
       INNER JOIN fb.fbmenu 
               ON fborders.fbmenuid = fbmenu.fbmenuid 
   INNER JOIN fb.fbrestaurant 
               ON fbrestaurant.fbrestaurantid = fbmenu.fbrestaurantid 
WHERE  fborders.status = 'completed' 
       AND ( Year(Str_to_date(date, '%d/%m/%Y')) = '2013' 
             AND fbrestaurant.fbrestaurantid = 'R0001' ) 
GROUP  BY fbmenuname, 
          Month(Str_to_date(date, '%d/%m/%Y')) 

Answer 1

As stated in this other answer:

You can use STR_TO_DATE() to convert the number to a date, and then back with MONTHNAME()

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

+---------------------------------+
| MONTHNAME(STR_TO_DATE(6, '%m')) |
+---------------------------------+
| June                            |
+---------------------------------+

Use Left Function to Trim the unwanted character

So try this

SELECT DISTINCT fbmenuname, 
                Sum(quantity) 
                AS ordercount, 
                Month(Str_to_date(date, '%d/%m/%Y')) 
                AS month, 
                Monthname(Str_to_date(Month(Str_to_date(date, '%d/%m/%Y')), '%m' 
                          )) AS MonthName 
FROM   fb.fborders 
       INNER JOIN fb.fbmenu 
               ON fborders.fbmenuid = fbmenu.fbmenuid 
       INNER JOIN fb.fbrestaurant 
               ON fbrestaurant.fbrestaurantid = fbmenu.fbrestaurantid 
WHERE  fborders.status = 'completed' 
       AND ( Year(Str_to_date(date, '%d/%m/%Y')) = '2013' 
             AND fbrestaurant.fbrestaurantid = 'R0001' ) 
GROUP  BY fbmenuname, 
          Month(Str_to_date(date, '%d/%m/%Y')) 

FIDDLE