The following two functions enable us to get the month name from a date in MySQL.

The MONTHNAME() Function

The MONTHNAME() function is designed specifically for returning the month name from a date. To do this, pass the date to the function when calling it. The full month name is returned.

Example:

SELECT MONTHNAME('2030-10-25');

Result:

October

The language used for the month name is controlled by the value of the lc_time_names system variable. See MONTHNAME() Examples for an example of how this affects the output. Also see How to Set the Locale for the Current Connection in MySQL for more information.

The DATE_FORMAT() Function

The DATE_FORMAT() function returns a given date in a specified format. To use this function, specify the date and the desired format when you call the function.

You can return the full month name by passing %M as the format string.

Example:

SELECT DATE_FORMAT('2030-12-25', '%M');

Result:

December

The language used for the month name is controlled by the value of the lc_time_names system variable.

Short Month Name

Passing %b to the DATE_FORMAT() function returns the short month name.

Example:

SELECT DATE_FORMAT('2030-12-25', '%b');

Result:

Dec

This could be replicated with the MONTHNAME() function by using LEFT() to grab the first 3 characters from the month name.

Example:

SELECT LEFT(MONTHNAME('2030-12-25'), 3);

Result:

Dec

The same concept could be achieved by converting the result of MONTHNAME() to a three character data type with a function like CAST().

However, while this approach works in en_US, it might not always work as expected in other languages. For example:

SET lc_time_names = 'th_TH';
SELECT 
    DATE_FORMAT('2030-12-25', '%b') AS "Short 1",
    LEFT(MONTHNAME('2030-12-25'), 3) AS "Short 2",
    MONTHNAME('2030-12-25') AS "Full";

Result:

+----------+-----------+-----------------------+
| Short 1  | Short 2   | Full                  |
+----------+-----------+-----------------------+
| ธ.ค.     | ธัน        | ธันวาคม                |
+----------+-----------+-----------------------+