MySQL convert month number to month name

3415 views mysql
1

Is it possible to convert an integer/column to month name in MySQL? For example, 1 becomes 'Jan' or 'January'. 13 and onwards give an error, or will be never used.

answered question

I think a simple Google search would get you the answer. Or just browse the MySQL date functions.

1 Answer

11

  • We can convert the given input number to MySQL date format (focusing on month only), using Str_To_Date() function.
  • Now, we simply need to use Monthname() function to extract the month name from the date.
  • This will work only when NO_ZERO_DATE mode is disabled.

Try:

SET sql_mode = ''; -- disable NO_ZERO_DATE mode
SELECT MONTHNAME(STR_TO_DATE(1, '%m'));

As @Felk suggested in comments, if we need to get shortened month name, we can use Date_Format() function instead:

SET sql_mode = ''; -- disable NO_ZERO_DATE mode
SELECT DATE_FORMAT(STR_TO_DATE(1, '%m'), '%b');

posted this

Have an answer?

JD

Please login first before posting an answer.