MariaDB DAY() Explained

In MariaDB, DAY() is a synonym for DAYOFMONTH(). It returns the day of the month from a given date.

It accepts one argument, which is the date you want to return 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:

DAY(date)

Where date is the date to extract the day from.

Example

Here’s an example:

SELECT DAY('2045-05-18');

Result:

+-------------------+
| DAY('2045-05-18') |
+-------------------+
|                18 |
+-------------------+

Datetime Values

It also works with datetime values:

SELECT DAY('2045-05-18 01:10:15');

Result:

+----------------------------+
| DAY('2045-05-18 01:10:15') |
+----------------------------+
|                         18 |
+----------------------------+

Zero Days

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

SELECT DAY('0000-00-00');

Result:

+-------------------+
| DAY('0000-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 DAY(20220308);

Result:

+---------------+
| DAY(20220308) |
+---------------+
|             8 |
+---------------+

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

SELECT DAY(220308);

Result:

+-------------+
| DAY(220308) |
+-------------+
|           8 |
+-------------+

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

SELECT DAY(220358);

Result:

+-------------+
| DAY(220358) |
+-------------+
|        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 
    DAY('2028/12/30'),
    DAY('2028,12,30'),
    DAY('2028:12:30'),
    DAY('2028;12!30');

Result (using vertical output):

DAY('2028/12/30'): 30
DAY('2028,12,30'): 30
DAY('2028:12:30'): 30
DAY('2028;12!30'): 30

Current Date

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

SELECT 
    NOW(),
    DAY(NOW());

Result:

+---------------------+------------+
| NOW()               | DAY(NOW()) |
+---------------------+------------+
| 2021-05-13 09:41:03 |         13 |
+---------------------+------------+

Invalid Arguments

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

SELECT DAY('Oops!');

Result:

+--------------+
| DAY('Oops!') |
+--------------+
|         NULL |
+--------------+

Missing Argument

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

SELECT DAY();

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 DAY('2030-01-25', '2045-05-08');

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 ' '2045-05-08')' at line 1