Below are 4 functions that enable you to return the month from a date in MariaDB.

The MONTHNAME() Function

The MONTHNAME() function returns the month name from a date. The language used for the name is controlled by the value of the lc_time_names system variable.

Example:

SELECT MONTHNAME('2023-07-25');

Result:

+-------------------------+
| MONTHNAME('2023-07-25') |
+-------------------------+
| July                    |
+-------------------------+

The MONTH() Function

The MONTH() function returns the month in the range 1 to 12 for January to December, or 0 for dates that have a zero month part (such as 0000-00-00).

Example:

SELECT MONTH('2023-07-25');

Result:

+---------------------+
| MONTH('2023-07-25') |
+---------------------+
|                   7 |
+---------------------+

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date. Therefore, you can use it to extract the month (as well as other units) from the date.

Example:

SELECT EXTRACT(MONTH FROM '2023-07-25');

Result:

+----------------------------------+
| EXTRACT(MONTH FROM '2023-07-25') |
+----------------------------------+
|                                7 |
+----------------------------------+

The DATE_FORMAT() Function

The DATE_FORMAT() function allows you to format a date based on a format string. The format string specifies how the date should be formatted.

You can therefore use this function to return the month (as well as any other unit) from the date. There are different format specifiers for returning the month in different formats. You can use different format specifiers to return the full month name, the short month name, the month index with 2 digits, and the month index with 1 or 2 digits (depending on whether the month is a single digit month or not).

Here’s an example that returns the month in various forms:

SELECT 
    DATE_FORMAT('2023-07-25', '%b') AS "%b",
    DATE_FORMAT('2023-07-25', '%c') AS "%c",
    DATE_FORMAT('2023-07-25', '%M') AS "%M",
    DATE_FORMAT('2023-07-25', '%m') AS "%m";

Result:

+------+------+------+------+
| %b   | %c   | %M   | %m   |
+------+------+------+------+
| Jul  | 7    | July | 07   |
+------+------+------+------+

See MariaDB Format Strings for a full list of format strings/specifiers.