How MONTH() Works in MariaDB

In MariaDB, MONTH() is a built-in date and time function that returns the month from a given date expression.

It accepts one argument, which is the date you want to extract the month from.

It returns the month as a number in the range 1 to 12 for January through December. If the date has a zero month part (e.g. 0000-00-00 or 2025-00-00), the result is 0.

Syntax

The syntax goes like this:

MONTH(date)

Where date is the date expression to get the month from.

Example

Here’s an example:

SELECT MONTH('2030-08-01');

Result:

+---------------------+
| MONTH('2030-08-01') |
+---------------------+
|                   8 |
+---------------------+

Datetime Values

It also works with datetime values:

SELECT MONTH('2030-08-01 10:30:45');

Result:

+------------------------------+
| MONTH('2030-08-01 10:30:45') |
+------------------------------+
|                            8 |
+------------------------------+

Zero Months

Zero months result in 0.

Example:

SELECT MONTH('2030-00-00');

Result:

+---------------------+
| MONTH('2030-00-00') |
+---------------------+
|                   0 |
+---------------------+

Numeric Dates

It’s also possible to pass dates as a number, as long as it makes sense as a date.

Example

SELECT MONTH(20301125);

Result:

+-----------------+
| MONTH(20301125) |
+-----------------+
|              11 |
+-----------------+

Or even the following (which uses a two-digit year):

SELECT MONTH(301125);

Result:

+---------------+
| MONTH(301125) |
+---------------+
|            11 |
+---------------+

But it must make sense as a date. Here’s what happens if I increase the day part to an invalid day:

SELECT MONTH(20301135);

Result:

+-----------------+
| MONTH(20301135) |
+-----------------+
|            NULL |
+-----------------+

Other Delimiters

You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:

SELECT 
    MONTH('2030/06/25'),
    MONTH('2030,06,25'),
    MONTH('2030:06:25'),
    MONTH('2030;06!25');

Result (using vertical output):

MONTH('2030/06/25'): 6
MONTH('2030,06,25'): 6
MONTH('2030:06:25'): 6
MONTH('2030;06!25'): 6

Current Date

We can pass NOW() as the datetime argument to use the current date:

SELECT 
    NOW(),
    MONTH(NOW());

Result:

+---------------------+--------------+
| NOW()               | MONTH(NOW()) |
+---------------------+--------------+
| 2021-05-16 15:00:06 |            5 |
+---------------------+--------------+

Invalid Arguments

When passed an invalid argument, MONTH() returns null:

SELECT MONTH('2030-65-78');

Result:

+---------------------+
| MONTH('2030-65-78') |
+---------------------+
|                NULL |
+---------------------+

Missing Argument

Calling MONTH() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT MONTH();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And another example:

SELECT MONTH('2030-12-10', '2031-12-10');

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' '2031-12-10')' at line 1