How to get the the month name in MariaDB

In MariaDB, you can use the DATE_FORMAT() function to return the month name of a specified date, either as a short name (such as Nov or Dec) or a long name (such as November or December).

Get short month names

In MariaDB, you can get the short month name of the specified date using the DATE_FORMAT() function with the %b format specifier, for example:

SELECT DATE_FORMAT('2022-01-01', '%b');

Output:

+---------------------------------+
| DATE_FORMAT('2022-01-01', '%b') |
+---------------------------------+
| Dec                             |
+---------------------------------+

Get long month names

In MariaDB, you can get the long month name of the specified date using the DATE_FORMAT() function with the %b format specifier, for example:

SELECT DATE_FORMAT('2022-01-01', '%M');

Output:

+---------------------------------+
| DATE_FORMAT('2022-01-01', '%M') |
+---------------------------------+
| December                        |
+---------------------------------+

Get the current month name

The following example shows how to get the current month name:

SELECT
  CURRENT_DATE,
  DATE_FORMAT(CURRENT_DATE, '%M') "Full Name",
  DATE_FORMAT(CURRENT_DATE, '%b') "Short Name";

Output:

+--------------+-----------+------------+
| CURRENT_DATE | Full Name | Short Name |
+--------------+-----------+------------+
| 2022-01-01   | December  | Dec        |
+--------------+-----------+------------+

Get all month names

The following example shows how to get all month names, including long names and short names:

SELECT
  '2022-01-01' AS "Day",
  DATE_FORMAT('2022-01-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-01-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-02-01' AS "Day",
  DATE_FORMAT('2022-02-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-02-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-03-01' AS "Day",
  DATE_FORMAT('2022-03-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-03-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-04-01' AS "Day",
  DATE_FORMAT('2022-04-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-04-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-05-01' AS "Day",
  DATE_FORMAT('2022-05-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-05-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-06-01' AS "Day",
  DATE_FORMAT('2022-06-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-06-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-07-01' AS "Day",
  DATE_FORMAT('2022-07-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-07-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-08-01' AS "Day",
  DATE_FORMAT('2022-08-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-08-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-09-01' AS "Day",
  DATE_FORMAT('2022-09-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-09-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-10-01' AS "Day",
  DATE_FORMAT('2022-10-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-10-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-11-01' AS "Day",
  DATE_FORMAT('2022-11-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-11-01', '%b') AS "Short Name"
UNION
SELECT
  '2022-12-01' AS "Day",
  DATE_FORMAT('2022-12-01', '%M') AS "Full Name",
  DATE_FORMAT('2022-12-01', '%b') AS "Short Name";

Output:

+------------+-----------+------------+
| Day        | Full Name | Short Name |
+------------+-----------+------------+
| 2022-01-01 | January   | Jan        |
| 2022-02-01 | February  | Feb        |
| 2022-03-01 | March     | Mar        |
| 2022-04-01 | April     | Apr        |
| 2022-05-01 | May       | May        |
| 2022-06-01 | June      | Jun        |
| 2022-07-01 | July      | Jul        |
| 2022-08-01 | August    | Aug        |
| 2022-09-01 | September | Sep        |
| 2022-10-01 | October   | Oct        |
| 2022-11-01 | November  | Nov        |
| 2022-12-01 | December  | Dec        |
+------------+-----------+------------+

Conclusion

This article discusses how to use the DATE_FORMAT() function to get month names:

The %b format specifier returns short month names.
The %M format specifier returns long month names.

You can check out more information about MariaDB date formatting specifiers.