How DAYOFMONTH() Works in MariaDB

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

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

The result is in the range 1 to 31. However, if the date has a zero day part (for example '0000-00-00' or '2021-00-00'), the result is 0.

Syntax

The syntax goes like this:

DAYOFMONTH(date)

Where date is the date to extract the day from.

Example

Here’s an example:

SELECT DAYOFMONTH('2030-01-25');

Result:

+--------------------------+
| DAYOFMONTH('2030-01-25') |
+--------------------------+
|                       25 |
+--------------------------+

Datetime Values

It also works with datetime values:

SELECT DAYOFMONTH('2030-01-25 10:30:45');

Result:

+-----------------------------------+
| DAYOFMONTH('2030-01-25 10:30:45') |
+-----------------------------------+
|                                25 |
+-----------------------------------+

Zero Days

Here’s an example that uses a zero day in the date:

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

Result:

+--------------------------+
| DAYOFMONTH('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 DAYOFMONTH(20300125);

Result:

+----------------------+
| DAYOFMONTH(20300125) |
+----------------------+
|                   25 |
+----------------------+

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

SELECT DAYOFMONTH(300125);

Result:

+--------------------+
| DAYOFMONTH(300125) |
+--------------------+
|                 25 |
+--------------------+

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

SELECT DAYOFMONTH(20300135);

Result:

+----------------------+
| DAYOFMONTH(20300135) |
+----------------------+
|                 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 
    DAYOFMONTH('2030/01/25'),
    DAYOFMONTH('2030,01,25'),
    DAYOFMONTH('2030:01:25'),
    DAYOFMONTH('2030;01!25');

Result (using vertical output):

DAYOFMONTH('2030/01/25'): 25
DAYOFMONTH('2030,01,25'): 25
DAYOFMONTH('2030:01:25'): 25
DAYOFMONTH('2030;01!25'): 25

Current Date

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

SELECT 
    NOW(),
    DAYOFMONTH(NOW());

Result:

+---------------------+-------------------+
| NOW()               | DAYOFMONTH(NOW()) |
+---------------------+-------------------+
| 2021-05-13 09:02:41 |                13 |
+---------------------+-------------------+

Invalid Arguments

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

SELECT DAYOFMONTH('Homer');

Result:

+---------------------+
| DAYOFMONTH('Homer') |
+---------------------+
|                NULL |
+---------------------+

Missing Argument

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

SELECT DAYOFMONTH();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFMONTH'

And another example:

SELECT DAYOFMONTH('2030-01-25', '2045-05-08');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYOFMONTH'