There are certain date functions in MySQL that the return a day name or month name. In particular, I’m referring to the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. These might return a value of say, November, or Monday, depending on the query being used. But the results could just as easily be returned in a different language if required.

The language that these functions use for their return value is derived from the lc_time_names system variable. You can view the value of this variable or set its SESSION value so that the results of these functions is in the desired language/locale.

Locale names have language and region subtags listed by the Internet Assigned Numbers Authority (IANA). Examples include en_US for English – United States, en_NZ for English – New Zealand, or es_PA for Spanish – Panama, etc (for a list of locales supported by MySQL, see Full List of Locales in MySQL).

In this article, I’ll show you how to find the current locale for your connection, change it, and then see how it affects the results of a query. I also show you a function that’s immune to this setting (but don’t worry, this function allows you to specify the locale).

View the Current Locale

First, let’s see what the current value is of the lc_time_names system variable.

SELECT @@lc_time_names;

Result:

+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+

So my current locale is en_US. This is actually the default value regardless of your system’s locale setting (but this can be changed at server startup or by settting the GLOBAL value).

Change the Locale

Now let’s change the locale and view the result.

SET lc_time_names = 'de_BE';
SELECT @@lc_time_names;

Result:

+-----------------+
| @@lc_time_names |
+-----------------+
| de_BE           |
+-----------------+

In this case, I changed the locale to de_BE, which is for German – Belgium.

Example of Usage

Here’s an example where I set the locale, then run a query that returns a month name. I then set the locale to a different value, then run the same query again.

1st Locale: English – United States

SET lc_time_names = 'en_US';
SELECT MONTHNAME('1999-10-03');

Result:

+-------------------------+
| MONTHNAME('1999-10-03') |
+-------------------------+
| October                 |
+-------------------------+

2nd Locale: Spanish – Spain

SET lc_time_names = 'es_ES';
SELECT MONTHNAME('1999-10-03');

Result:

+-------------------------+
| MONTHNAME('1999-10-03') |
+-------------------------+
| octubre                 |
+-------------------------+

The FORMAT() Function

The value of lc_time_names doesn’t affect the FORMAT() function, but this function accepts a 3rd argument that allows you to specify the locale. Here’s an example of what I mean.

SET lc_time_names = 'de_DE';
SELECT 
    FORMAT(1234, 0),
    FORMAT(1234, 0, 'de_DE');

Result:

+-----------------+--------------------------+
| FORMAT(1234, 0) | FORMAT(1234, 0, 'de_DE') |
+-----------------+--------------------------+
| 1,234           | 1.234                    |
+-----------------+--------------------------+

So even though I set lc_time_names to de_DE first, the initial call to FORMAT() ignored that. When I called the function the second time, I explicitly specified that same language/locale as the 3rd argument, and it worked.