MYSQL: How to convert a string into a month (Number)

Question

I have the short version of months: JAN, FEB, MAR, etc.

and would like to convert them to it's respective numeric value: 1, 2, 3, etc

Also, I would like to be able to change back and forth between the numeric-month-value, to a "short" month name (JAN, FEB, MAR) and it's long version (January, February, March, etc)

NOTE: As @dipu-raj pointed out, this is not a duplicate because I am asking the opposite to MySQL MONTHNAME() from numbers and the answer IS different as well BECAUSE it requires different functions

Answer 1

To convert abbrevation to full month name use:

mysql> select monthname(str_to_date('Mar','%b'));
+------------------------------------+
| monthname(str_to_date('Mar','%b')) |
+------------------------------------+
| March                              |
+------------------------------------+

To convert abbrevation to number use:

mysql> select month(str_to_date('Mar','%b'));
+--------------------------------+
| month(str_to_date('Mar','%b')) |
+--------------------------------+
|                              3 |
+--------------------------------+