How to get month and day name in different languages ​​in MariaDB

In MariaDB, the DATE_FORMAT() function is used to format output datetime values.

You can use different format specifiers to get output in different formats, including month names and day names.

At the same time, the DATE_FORMAT() function also receives an optional parameter for specifying the language. This allows you to get output in a specific language. The default language is English.

To query the current language used by DATE_FORMAT(), use the following statement:

SELECT @@lc_time_names;

Output:

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

Get month name

We have discussed how to get the month name of the specified date in the previous tutorial, in this example we mainly discuss how to get the month name in different languages.

To get the month name, use the %M format specifier.

The following statement outputs the names of the month of January in the default language, English, Spanish, and Simplified Chinese.

SELECT
  DATE_FORMAT('2025-01-01', '%M') AS "Default",
  DATE_FORMAT('2025-01-01', '%M', 'en_US') AS "es_US",
  DATE_FORMAT('2025-01-01', '%M', 'es_ES') AS "es_ES",
  DATE_FORMAT('2025-01-01', '%M', 'zh_CN') AS "zh_CN";

Output:

+---------+---------+-------+--------+
| Default | es_US   | es_ES | zh_CN  |
+---------+---------+-------+--------+
| January | January | enero | 一月   |
+---------+---------+-------+--------+

Get day names

We have discussed how to get the day name of a specified date in the previous tutorial, in this example we mainly discuss how to get the day name in different languages.

To get the day name, use the %W format specifier.

The following statement outputs the day names in the default language, English, Spanish, and Simplified Chinese.

SELECT
  DATE_FORMAT('2025-01-01', '%W') AS "Default",
  DATE_FORMAT('2025-01-01', '%W', 'en_US') AS "es_US",
  DATE_FORMAT('2025-01-01', '%W', 'es_ES') AS "es_ES",
  DATE_FORMAT('2025-01-01', '%W', 'zh_CN') AS "zh_CN";

Output:

+-----------+-----------+------------+-----------+
| Default   | es_US     | es_ES      | zh_CN     |
+-----------+-----------+------------+-----------+
| Wednesday | Wednesday | miércoles  | 星期三    |
+-----------+-----------+------------+-----------+

Conclusion

MariaDB DATE_FORMAT() is used to format the output datetime and supports specifying different languages.