In MySQL, you can use the DATE_FORMAT() function with the %b format specifier to return the short month name. For example, you can return Jan or Feb instead of January or February.

Example

SELECT DATE_FORMAT('2035-01-18', '%b');

Result:

Jan

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

SELECT 
    DATE_FORMAT('2035-01-18', '%b') AS "1",
    DATE_FORMAT('2035-02-18', '%b') AS "2",
    DATE_FORMAT('2035-03-18', '%b') AS "3",
    DATE_FORMAT('2035-04-18', '%b') AS "4",
    DATE_FORMAT('2035-05-18', '%b') AS "5",
    DATE_FORMAT('2035-06-18', '%b') AS "6",
    DATE_FORMAT('2035-07-18', '%b') AS "7",
    DATE_FORMAT('2035-08-18', '%b') AS "8",
    DATE_FORMAT('2035-09-18', '%b') AS "9",
    DATE_FORMAT('2035-10-18', '%b') AS "10",
    DATE_FORMAT('2035-11-18', '%b') AS "11",
    DATE_FORMAT('2035-12-18', '%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 all about returning the short month name.

See MySQL Date Format Specifiers for a list of format specifiers that can be used with DATE_FORMAT().

Shorten the Full Month Name

If you need to shorten a full month name, you can always use a function like LEFT() to return just the first three characters (or however many you need).

Example:

SELECT 
    DATE_FORMAT('2035-09-18', '%M') AS Full,
    LEFT(DATE_FORMAT('2035-09-18', '%M'), 3) AS Short;

Result:

+-----------+-------+
| Full      | Short |
+-----------+-------+
| September | Sep   |
+-----------+-------+

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

SELECT 
    DATE_FORMAT('2035-09-18', '%M') AS Full,
    CAST(DATE_FORMAT('2035-09-18', '%M') AS CHAR(3)) AS Short;

Result:

+-----------+-------+
| Full      | Short |
+-----------+-------+
| September | Sep   |
+-----------+-------+
1 row in set, 1 warning (0.00 sec)

In this case, we get a warning because we’re truncating a longer value:

show warnings;

Result:

+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1292 | Truncated incorrect CHAR(3) value: 'September' |
+---------+------+------------------------------------------------+

Shortening the month name like this can be fine when working with languages such as English, but be mindful that it may not return the same result as when using the %b format specifier.

To demonstrate what I mean, here’s what happens when I switch my session to use the Thai language:

SET lc_time_names = 'th_TH';
SELECT 
    DATE_FORMAT('2023-10-25', '%M') AS Full,
    LEFT(DATE_FORMAT('2023-10-25', '%M'), 3) AS Truncated,
    DATE_FORMAT('2023-10-25', '%b') AS Short;

Result:

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

Truncating the full month name returns a different result than using the %b format specifier to return the short month name.