In MariaDB, you can use the DATE_FORMAT() function to return various date parts from a date. One of the things you can return is the short month name. For example Nov or Dec (instead of November or December).

The “Short Month Name” Format Specifier (%b)

The key to returning the short month name when using the DATE_FORMAT() function is to use the applicable format specifier.

In MariaDB, the format specifier for the short month name is: %b

Example

Here’s an example to demonstrate:

SELECT DATE_FORMAT('2023-12-25', '%b');

Result:

+---------------------------------+
| DATE_FORMAT('2023-12-25', '%b') |
+---------------------------------+
| Dec                             |
+---------------------------------+

Here’s another example that runs through the various months in the year:

SELECT 
    DATE_FORMAT('2023-01-25', '%b') AS "1",
    DATE_FORMAT('2023-02-25', '%b') AS "2",
    DATE_FORMAT('2023-03-25', '%b') AS "3",
    DATE_FORMAT('2023-04-25', '%b') AS "4",
    DATE_FORMAT('2023-05-25', '%b') AS "5",
    DATE_FORMAT('2023-06-25', '%b') AS "6",
    DATE_FORMAT('2023-07-25', '%b') AS "7",
    DATE_FORMAT('2023-08-25', '%b') AS "8",
    DATE_FORMAT('2023-09-25', '%b') AS "9",
    DATE_FORMAT('2023-10-25', '%b') AS "10",
    DATE_FORMAT('2023-11-25', '%b') AS "11",
    DATE_FORMAT('2023-12-25', '%b') AS "12";

Result (using vertical output):

 1: Jan
 2: Feb
 3: Mar
 4: Apr
 5: May
 6: Jun
 7: Jul
 8: Aug
 9: Sep
10: Oct
11: Nov
12: Dec

You can return other date and time units too, but this article is specifically about returning the short month name. See MariaDB Format Strings for a list of format specifiers that can be used with DATE_FORMAT().

Other Approaches

There are other ways to get the short month name from a date that involve chopping off the first part of the month.

However, this can be more prone to error than the above method (plus it’s more convoluted). You could get some unexpected results when using this method, especially when working with different languages.

Here are some examples to demonstrate what I mean.

Instead of using the %b format specifier like we did in the previous example, we could use the %M format specifier to return the full month name, then return shorten that result with a function like LEFT().

Example:

SELECT 
    DATE_FORMAT('2023-10-25', '%M') AS Full,
    LEFT(DATE_FORMAT('2023-10-25', '%M'), 3) AS Short;

Result:

+---------+-------+
| Full    | Short |
+---------+-------+
| October | Oct   |
+---------+-------+

We could alternatively use a function like CAST() to convert to a data type with only three characters, such as this:

SELECT 
    DATE_FORMAT('2023-10-25', '%M') AS Full,
    CAST(DATE_FORMAT('2023-10-25', '%M') AS CHAR(3)) AS Short;

Result:

+---------+-------+
| Full    | Short |
+---------+-------+
| October | Oct   |
+---------+-------+

This works fine when working with the English language (and perhaps some other languages). But let’s see what happens when we switch to a different language – say, Thai:

SELECT 
    DATE_FORMAT('2023-10-25', '%M', 'th_TH') AS Full,
    LEFT(DATE_FORMAT('2023-10-25', '%M', 'th_TH'), 3) AS Short;

Result:

+--------------------+-----------+
| Full               | Short     |
+--------------------+-----------+
| ตุลาคม              | ตุล        |
+--------------------+-----------+

However, here’s what happens when we use the %b format specifier to return the short month name:

SELECT 
    DATE_FORMAT('2023-10-25', '%M', 'th_TH') AS Full,
    DATE_FORMAT('2023-10-25', '%b', 'th_TH') AS Short;

Result:

+--------------------+----------+
| Full               | Short    |
+--------------------+----------+
| ตุลาคม              | ต.ค.     |
+--------------------+----------+

It returns a different result.

So I’d recommend using the %b format specifier whenever possible.