MariaDB has quite a number of functions that return the day from a date. It all depends on how you want to do it, and what you mean by “day”.

MariaDB needs to know whether you want the day name, the day of the week number, the day of the month, day of year, etc.

Below are 8 functions that enable you to return the day from a date in MariaDB, in its various forms.

The DAYNAME() Function

The DAYNAME() function returns the weekday name.

Example:

SELECT DAYNAME('2023-07-25');

Result:

+-----------------------+
| DAYNAME('2023-07-25') |
+-----------------------+
| Tuesday               |
+-----------------------+

The DAYOFMONTH() Function

The DAYOFMONTH() function returns the day of the month number.

Example:

SELECT DAYOFMONTH('2023-07-25');

Result:

+--------------------------+
| DAYOFMONTH('2023-07-25') |
+--------------------------+
|                       25 |
+--------------------------+

The DAY() Function

The DAY() function is a synonym for the DAYOFMONTH() function.

Example:

SELECT DAY('2023-07-25');

Result:

+-------------------+
| DAY('2023-07-25') |
+-------------------+
|                25 |
+-------------------+

As expected, the same result as DAYOFMONTH().

The DAYOFWEEK() Function

The DAYOFWEEK() function returns the day of the week index for the date, as specified by the ODBC standard (1 = Sunday, 2 = Monday, …, 7 = Saturday).

Example:

SELECT DAYOFWEEK('2023-07-25');

Result:

+-------------------------+
| DAYOFWEEK('2023-07-25') |
+-------------------------+
|                       3 |
+-------------------------+

See WEEKDAY() below for different indexing.

The WEEKDAY() Function

The WEEKDAY() function is similar to DAYOFWEEK() in that it returns the week index for the date. The difference is that it uses a different index numbering (0 = Monday, 1 = Tuesday, … 6 = Sunday).

Example:

SELECT WEEKDAY('2023-07-25');

Result:

+-----------------------+
| WEEKDAY('2023-07-25') |
+-----------------------+
|                     1 |
+-----------------------+

The DAYOFYEAR() Function

The DAYOFYEAR() function returns the day of the year for the date, in the range 1 to 366.

Example:

SELECT DAYOFYEAR('2023-07-25');

Result:

+-------------------------+
| DAYOFYEAR('2023-07-25') |
+-------------------------+
|                     206 |
+-------------------------+

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date. Therefore, you can use it to extract the day (as well as other units) from the date.

Example:

SELECT EXTRACT(DAY FROM '2023-07-25');

Result:

+--------------------------------+
| EXTRACT(DAY FROM '2023-07-25') |
+--------------------------------+
|                             25 |
+--------------------------------+

The DATE_FORMAT() Function

The DATE_FORMAT() function allows you to format a date based on a format string. The format string specifies how the date should be formatted.

You can therefore use this function to return just the day from the date. Of course, you can return other units too, but this article is about returning the day.

Here’s an example that returns the day in various forms:

SELECT 
    DATE_FORMAT('2023-07-25', '%a') AS "%a",
    DATE_FORMAT('2023-07-25', '%D') AS "%D",
    DATE_FORMAT('2023-07-25', '%d') AS "%d",
    DATE_FORMAT('2023-07-25', '%e') AS "%e",
    DATE_FORMAT('2023-07-25', '%j') AS "%j",
    DATE_FORMAT('2023-07-25', '%W') AS "%W",
    DATE_FORMAT('2023-07-25', '%w') AS "%w";

Result:

+------+------+------+------+------+---------+------+
| %a   | %D   | %d   | %e   | %j   | %W      | %w   |
+------+------+------+------+------+---------+------+
| Tue  | 25th | 25   | 25   | 206  | Tuesday | 2    |
+------+------+------+------+------+---------+------+

See MariaDB Format Strings for an explanation of each of these format strings/specifiers.