Omar Omar - 1 year ago 142
MySQL Question

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

I have the short version of months:

, 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 (
) 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 Source

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 |
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download