MariaDB has several functions that enable you to extract various date and time parts from date/time values. You can use these to separate each date/time component into its own column if required.

Below are three ways to extract the year, month, and day from a date value in MariaDB.

The YEAR(), MONTH(), and DAY() Functions

The YEAR(), MONTH(), and DAY() functions extract the year, month, and day from a date or datetime value respectively.

Here’s an example of using these functions to return each date component in its own column:

SELECT 
    YEAR('2025-08-30') AS "Year",
    MONTH('2025-08-30') AS "Month",
    DAY('2025-08-30') AS "Day";

Result:

+------+-------+------+
| Year | Month | Day  |
+------+-------+------+
| 2025 |     8 |   30 |
+------+-------+------+

The DAY() function is actually a synonym for DAYOFMONTH(), so either one will return the same result.

There’s also a WEEKDAY() function, a DAYOFWEEK() function, a DAYOFYEAR() function, and a DAYNAME() function, each of which return a different representation of the day.

Plus there’s a MONTHNAME() function that returns the month name, instead of its number.

Here’s another example that includes those functions:

SELECT 
    YEAR('2025-08-30') AS "YEAR",
    MONTH('2025-08-30') AS "MONTH",
    MONTHNAME('2025-08-30') AS "MONTHNAME",
    DAY('2025-08-30') AS "DAY",
    DAYOFMONTH('2025-08-30') AS "DAYOFMONTH",
    WEEKDAY('2025-08-30') AS "WEEKDAY",
    DAYOFWEEK('2025-08-30') AS "DAYOFWEEK",
    DAYOFYEAR('2025-08-30') AS "DAYOFYEAR",
    DAYNAME('2025-08-30') AS "DAYNAME";

Result (using vertical output):

      YEAR: 2025
     MONTH: 8
 MONTHNAME: August
       DAY: 30
DAYOFMONTH: 30
   WEEKDAY: 5
 DAYOFWEEK: 7
 DAYOFYEAR: 242
   DAYNAME: Saturday

You can also use functions such as WEEK() to return the week number, and QUARTER() to return the quarter.

Example:

SELECT 
    WEEK('2025-08-30') AS "Week",
    QUARTER('2025-08-30') AS "Quarter";

Result:

+------+---------+
| Week | Quarter |
+------+---------+
|   34 |       3 |
+------+---------+

The WEEK() function accepts a second argument that allows you to specify the mode. This can change the resulting week number, depending on the actual date. See How WEEK() Works in MariaDB for more information and an example.

The EXTRACT() Function

The EXTRACT() function allows you to extract a specified unit from the date/time value. Therefore, you can use it to extract the year, month, and day from the date (as well as week and quarter if required).

Example:

SELECT 
    EXTRACT(YEAR FROM '2023-03-12') AS "Year",
    EXTRACT(MONTH FROM '2023-03-12') AS "Month",
    EXTRACT(DAY FROM '2023-03-12') AS "Day",
    EXTRACT(WEEK FROM '2023-03-12') AS "Week",
    EXTRACT(QUARTER FROM '2023-03-12') AS "Quarter";

Result:

+------+-------+------+------+---------+
| Year | Month | Day  | Week | Quarter |
+------+-------+------+------+---------+
| 2023 |     3 |   12 |   11 |       1 |
+------+-------+------+------+---------+

The DATE_FORMAT() Function

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

We can therefore use this function to return the year, month, day, and week from a date.

Example:

SELECT 
    DATE_FORMAT('2035-12-08', '%Y') AS "Year",
    DATE_FORMAT('2035-12-08', '%m') AS "Month",
    DATE_FORMAT('2035-12-08', '%d') AS "Day",
    DATE_FORMAT('2035-12-08', '%U') AS "Week";

Result:

+------+-------+------+------+
| Year | Month | Day  | Week |
+------+-------+------+------+
| 2035 | 12    | 08   | 48   |
+------+-------+------+------+

There are several possible format specifiers for each date part. Each date part can return a different value, depending on the actual format specifier provided. For example, you can return the full month name using %M instead of %m.

See MariaDB Format Strings for a full list of format strings/specifiers that can be used with DATE_FORMAT().