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