MySQL convert month number to month name

user_2156512 0
Viewed 5215

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.

date-conversion date mysql
1 Answers
  • 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');

If you don't want to disable the NO_ZERO_DATE mode, then you can create any random date using the month and call Monthname():

SELECT MONTHNAME(CONCAT('2018-',3,'-1')); -- 3 is the input number
user_24693080
Related