In MariaDB, you can use the DATE_FORMAT() function to return the month name and day name from a date.

This function accepts an optional argument that let’s you specify the language that is used for the result.

Example

Here’s an example to demonstrate:

SELECT 
    DATE_FORMAT('2035-12-25', '%W, %M') AS "Default Language",
    DATE_FORMAT('2035-12-25', '%W, %M', 'es_ES') AS "Spanish (Spain)";

Result:

+-------------------+-------------------+
| Default Language  | Spanish (Spain)   |
+-------------------+-------------------+
| Tuesday, December | martes, diciembre |
+-------------------+-------------------+

The format string for the function consists of any number of format specifiers to return various date parts from the date. In this example, my format strings consisted of two format specifiers (one to return the day name, one to return the month name).

I specified the locale in the second column (which sets the language), and so it was returned in the language for that locale. In this case, I specified es_ES, which is the locale for the Spanish language in Spain.

I didn’t specify the language in the first column, and so the function used the lc_time_names system variable. The default is always en_US regardless of the system’s locale setting, but this can be changed.

Here’s how to see your current locale:

SELECT @@lc_time_names;

Result:

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

See Date & Time Locales Available in MariaDB for a full list of locales supported by MariaDB.

You can also return a list of supported locales with a query. See How to Show all Locales in MariaDB for instructions on doing this.