Omar Omar - 13 days ago 8
MySQL Question

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

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

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 |
+--------------------------------+