MySQL MONTHNAME() Function

Summary: in this tutorial, you will learn how to use the MySQL MONTHNAME() function to return the name of a month for a specific date.

Introduction to the MySQL MONTHNAME() function

The MONTHNAME() function returns the name e of a month for a date.

Here’s the syntax of the MONTHNAME() function:

MONTHNAME(date)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• date: This is the date on which you want to get the month’s name.

The MONTHNAME() function returns a string that represents the name of the month for the given date.

If the date is NULL, the MONTHNAME() function returns NULL.

MySQL MONTHNAME() function example

Let’s take some examples of using the MONTHNAME() function.

1) Simple MONTHNAME() function example

The following example uses the MONTHNAME() function to get the month name of the date '2023-10-16':

SELECT MONTHNAME('2023-10-16') Month;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| Month   |
+---------+
| October |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

2) Using the MONTHNAME function with table data

We’ll use the orders table from the sample database:

The following example uses the MONTHNAME to count the number of orders by month in 2004:

SELECT 
  MONTHNAME(orderDate) month, 
  COUNT(*) orderCount 
FROM 
  orders 
WHERE YEAR(orderDate) = 2004
GROUP BY 
  MONTHNAME(orderDate)Code language: SQL (Structured Query Language) (sql)

Output:

+-----------+------------+
| month     | orderCount |
+-----------+------------+
| January   |          8 |
| February  |         11 |
| March     |          8 |
| April     |         10 |
| May       |          8 |
| June      |         12 |
| July      |         11 |
| August    |         12 |
| September |         12 |
| October   |         13 |
| November  |         33 |
| December  |         13 |
+-----------+------------+
12 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Language

The system variable @@lc_time_names controls the language of the month’s name.

To view the current locale, you use the following statement:

SELECT @@lc_time_names;Code language: SQL (Structured Query Language) (sql)

Output:

+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

The current locale is en_US. Therefore, you will see the month name in English like January, February, etc.

To get the month’s name in a different language, you need to change the current locale to the desired one.

For example, the following statement changes the current locale to fr_FR:

SET @@lc_time_names = 'fr_FR';Code language: SQL (Structured Query Language) (sql)

Now, if you retrieve the month’s name of a date, you’ll get the month’s name in French instead of English. For example:

SELECT MONTHNAME('2023-10-16') Month;Code language: SQL (Structured Query Language) (sql)

Output:

+---------+
| Month   |
+---------+
| octobre |
+---------+
1 row in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

Summary

• Use the MONTHNAME() function to get the month’s name of a specific date.
• Use the variable @@lc_time_names to control the language of the month’s name.