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

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.

Passing %b as the format string returns the short month name.

Example:

SELECT DATE_FORMAT('2023-09-20', '%b');

Result:

+---------------------------------+
| DATE_FORMAT('2023-09-20', '%b') |
+---------------------------------+
| Sep                             |
+---------------------------------+

The MONTHNAME() Function

The MONTHNAME() function returns the full month name from a date. It doesn’t return the short month name. However, it can be passed to the LEFT() function to return just the first 3 characters from the month name if required.

Example:

SELECT LEFT(MONTHNAME('2023-09-20'), 3);

Result:

+----------------------------------+
| LEFT(MONTHNAME('2023-09-20'), 3) |
+----------------------------------+
| Sep                              |
+----------------------------------+

This approach doesn’t necessarily work in all languages. For example, here’s what happens when using Thai:

SET lc_time_names = 'th_TH';
SELECT 
    DATE_FORMAT('2023-09-20', '%b') AS "Short 1",
    LEFT(MONTHNAME('2023-09-20'), 3) AS "Short 2",
    MONTHNAME('2023-09-20') AS "Full";

Result:

+----------+-----------+-----------------------+
| Short 1  | Short 2   | Full                  |
+----------+-----------+-----------------------+
| ก.ย.     | กัน       | กันยายน               |
+----------+-----------+-----------------------+