2 Ways to Get a Month Name from a Date in MySQL

Use MONTHNAME(date) to return the full month name, or DATE_FORMAT(date, '%M') to include the month name in a formatted string. Use %b with DATE_FORMAT() for an abbreviated month name. MySQL’s lc_time_names session setting controls the language returned by all three forms. See the MySQL date and time function reference and locale support documentation.

1. Use MONTHNAME() for the full name

Pass a date or datetime expression to MONTHNAME():

SELECT MONTHNAME('2024-05-12') AS month_name;

With lc_time_names set to en_US, this returns May. Use CURDATE() to get the name for the current date:

SELECT MONTHNAME(CURDATE()) AS current_month;

See SQLiz’s MySQL MONTHNAME() reference.

2. Format the full or abbreviated name with DATE_FORMAT()

Use %M for the full month name or %b for the abbreviated name:

SELECT
    DATE_FORMAT('2024-05-12', '%M') AS full_month,
    DATE_FORMAT('2024-05-12', '%b') AS abbreviated_month;

With lc_time_names set to en_US, the results are May and May. DATE_FORMAT() returns a string, so use it for display or text output; use MONTH(date) when the query needs a numeric month from 1 to 12. See SQLiz’s MySQL DATE_FORMAT() reference.

Change the language of month names

lc_time_names controls the language used by MONTHNAME() and the %M and %b specifiers. Its default is en_US, regardless of the operating system locale. To use another supported locale for the current connection, set the session variable:

SET lc_time_names = 'es_MX';
SELECT MONTHNAME('2024-05-12') AS month_name;

This returns mayo for that locale. The same locale setting affects DATE_FORMAT() month names. Use MONTH() or the original date value for chronological sorting; month names sort alphabetically, not by calendar order.