Search code examples
mysqlstring-conversiondate-conversion

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


Solution

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