Here are three ways to return the month name from a date in MariaDB.

The MONTHNAME() Function

The MONTHNAME() function is designed specifically for returning the month name from a date. To do this, pass the date to the function when calling it. The full month name will be returned.

Example:

SELECT MONTHNAME('2021-08-19');

Result:

+-------------------------+
| MONTHNAME('2021-08-19') |
+-------------------------+
| August                  |
+-------------------------+

The language used for the month name is controlled by the value of the lc_time_names system variable. See MONTHNAME() for an example of how this affects the output.

The DATE_FORMAT() Function

The DATE_FORMAT() function formats a given date into a specified format. You specify the date and the format when you call the function.

You can return the full month name by passing %M as the format string.

Example:

SELECT DATE_FORMAT('2021-08-19', '%M');

Result:

+---------------------------------+
| DATE_FORMAT('2021-08-19', '%M') |
+---------------------------------+
| August                          |
+---------------------------------+

As with MONTHNAME(), the language used for the month name is controlled by the value of the lc_time_names system variable. However, the DATE_FORMAT() function accepts an optional third argument that allows you to specify the locale.

Here’s an example of specifying a locale:

SELECT DATE_FORMAT('2021-08-19', '%M', 'ca_ES');

Result:

+------------------------------------------+
| DATE_FORMAT('2021-08-19', '%M', 'ca_ES') |
+------------------------------------------+
| agost                                    |
+------------------------------------------+

Short Month Name

Passing %b to the DATE_FORMAT() function returns the short month name.

Example:

SELECT DATE_FORMAT('2021-08-19', '%b');

Result:

+---------------------------------+
| DATE_FORMAT('2021-08-19', '%b') |
+---------------------------------+
| Aug                             |
+---------------------------------+

This could be replicated with the MONTHNAME() function by using LEFT() to grab the first 3 characters from the month name.

Example:

SELECT LEFT(MONTHNAME('2021-08-19'), 3);

Result:

+----------------------------------+
| LEFT(MONTHNAME('2021-08-19'), 3) |
+----------------------------------+
| Aug                              |
+----------------------------------+

The same concept could be achieved by converting the result of MONTHNAME() to a three character data type with a function like CAST().

However, while this approach works in en_US, it might not always work in other languages. For example:

SET lc_time_names = 'th_TH';
SELECT 
    DATE_FORMAT('2021-08-19', '%b') AS "Short 1",
    LEFT(MONTHNAME('2021-08-19'), 3) AS "Short 2",
    MONTHNAME('2021-08-19') AS "Full";

Result:

+----------+-----------+-----------------------+
| Short 1  | Short 2   | Full                  |
+----------+-----------+-----------------------+
| ส.ค.     | สิง        | สิงหาคม                |
+----------+-----------+-----------------------+