Search code examples
mysqldatedate-conversion

MySQL convert month number to month name

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.

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