MariaDB includes many functions for working with dates. Some of these extract certain parts from such dates and return them as a string or integer.

Below are 11 functions that you can use to return various date parts in MariaDB.

The Functions

First, here are the functions:

• DATE_FORMAT()
• EXTRACT()
• DAYNAME()
• DAYOFMONTH()
• DAY()
• DAYOFWEEK()
• WEEKDAY()
• DAYOFYEAR()
• MONTH()
• MONTHNAME()
• YEAR()

Below are examples of each function.

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 construct your own format string based on one or more format specifiers.

Here’s an example of using DATE_FORMAT() to return the day, month, and year from a date:

SELECT DATE_FORMAT('2023-07-25', '%W, %D %M %Y');

Result:

+-------------------------------------------+
| DATE_FORMAT('2023-07-25', '%W, %D %M %Y') |
+-------------------------------------------+
| Tuesday, 25th July 2023                   |
+-------------------------------------------+

The good thing about this function is that you can return many variations of the date. For example, you can return just the short day and month names:

SELECT DATE_FORMAT('2023-07-25', '%a, %D %b %Y');

Result:

+-------------------------------------------+
| DATE_FORMAT('2023-07-25', '%a, %D %b %Y') |
+-------------------------------------------+
| Tue, 25th Jul 2023                        |
+-------------------------------------------+

Or you can return the day month numbers:

SELECT DATE_FORMAT('2023-07-25', '%d/%c/%Y');

Result:

+---------------------------------------+
| DATE_FORMAT('2023-07-25', '%d/%c/%Y') |
+---------------------------------------+
| 25/7/2023                             |
+---------------------------------------+

See this full list of format specifiers for a full rundown on the format specifiers you can use to construct a format string.

DATE_FORMAT() also accepts an optional locale argument that you can use to specify the language of the day and month names. It’s quite a flexible function. See DATE_FORMAT() for more examples.

The EXTRACT() Function

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

Example:

SELECT 
    EXTRACT(DAY FROM '2023-07-25') AS Day,
    EXTRACT(MONTH FROM '2023-07-25') AS Month,
    EXTRACT(YEAR FROM '2023-07-25') AS Year;

Result:

+------+-------+------+
| Day  | Month | Year |
+------+-------+------+
|   25 |     7 | 2023 |
+------+-------+------+

Functions for Returning a Specific Date Unit

MariaDB also has a bunch of more specific functions that return a specific date unit.

Below is a list of the functions that return the day, month, and year from a date.

The DAYNAME() Function

There are quite a few functions for returning the day in MariaDB (and most other DBMSs). This is because, 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.

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 actually 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 MONTH() Function

The MONTH() function returns the month in the range 1 to 12 for January to December, or 0 for dates that have a zero month part (such as 0000-00-00).

Example:

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

Result:

+---------------------+
| MONTH('2023-07-25') |
+---------------------+
|                   7 |
+---------------------+

The MONTHNAME() Function

As the name suggests, the MONTHNAME() function returns the month name. The language used for the name is controlled by the value of the lc_time_names system variable.

Example:

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

Result:

+-------------------------+
| MONTHNAME('2023-07-25') |
+-------------------------+
| July                    |
+-------------------------+

The YEAR() Function

The YEAR() function returns the year part of the date.

Example:

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

Result:

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