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