Below are 4 functions that enable you to return the year from a date in MariaDB. Three functions return just the year, and one returns both the year and week.

The YEAR() Function

The YEAR() function returns the year for a given date. The result is in the range 1000 to 9999, or 0 for dates that have a zero year (such as 0000-00-00).

Example:

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

Result:

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

The EXTRACT() Function

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

Example:

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

Result:

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

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 the year (as well as any other unit) from the date. There are different format specifiers for returning the year in different formats. For example a four digit year, two digit year, etc.

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

SELECT 
    DATE_FORMAT('2023-01-01', '%X') AS "%X",
    DATE_FORMAT('2023-01-01', '%x') AS "%x",
    DATE_FORMAT('2023-01-01', '%Y') AS "%Y",
    DATE_FORMAT('2023-01-01', '%y') AS "%y";

Result:

+------+------+------+------+
| %X   | %x   | %Y   | %y   |
+------+------+------+------+
| 2023 | 2022 | 2023 | 23   |
+------+------+------+------+

Notice that %x returned a different year number than the others.

Here’s a description of each of those format specifiers, which explains why %x returned a different result:

Format Specifier	Description
%X	Year with 4 digits when first day of the week is Sunday. Used with %V.
%x	Year with 4 digits when first day of the week is Monday. Used with %v.
%Y	Year with 4 digits.
%y	Year with 2 digits.

And here’s a description of %V and %v as referred to in the above table:

Format Specifier	Description
%V	Week number (01-53), when first day of the week is Sunday. Used with %X.
%v	Week number (01-53), when first day of the week is Monday. Used with %x.

So we could add those format specifiers to the above example, and get the following:

SELECT 
    DATE_FORMAT('2023-01-01', '%X, %V') AS "%X, %V",
    DATE_FORMAT('2023-01-01', '%x, %v') AS "%x, %v";

Result:

+----------+----------+
| %X, %V   | %x, %v   |
+----------+----------+
| 2023, 01 | 2022, 52 |
+----------+----------+

Note that %x won’t always return a different year – it depends on the actual date being used. Sometimes it’s %X that returns a different year to the others.

Let’s move the date forward a year:

SELECT 
    DATE_FORMAT('2024-01-01', '%X') AS "%X",
    DATE_FORMAT('2024-01-01', '%x') AS "%x",
    DATE_FORMAT('2024-01-01', '%Y') AS "%Y",
    DATE_FORMAT('2024-01-01', '%y') AS "%y";

Result:

+------+------+------+------+
| %X   | %x   | %Y   | %y   |
+------+------+------+------+
| 2023 | 2024 | 2024 | 24   |
+------+------+------+------+

This time it’s %X that’s the odd one out. As explained in the above table, it depends on whether the first day of the week is Sunday or Monday.

Of course, if we move further into the year, all format specifiers return the same year:

SELECT 
    DATE_FORMAT('2024-12-12', '%X') AS "%X",
    DATE_FORMAT('2024-12-12', '%x') AS "%x",
    DATE_FORMAT('2024-12-12', '%Y') AS "%Y",
    DATE_FORMAT('2024-12-12', '%y') AS "%y";

Result:

+------+------+------+------+
| %X   | %x   | %Y   | %y   |
+------+------+------+------+
| 2024 | 2024 | 2024 | 24   |
+------+------+------+------+

See MariaDB Format Strings for a full list of format strings/specifiers.

The YEARWEEK() Function

The YEARWEEK() function returns the year and week for a given date.

Example:

SELECT YEARWEEK('2023-01-01');

Result:

+------------------------+
| YEARWEEK('2023-01-01') |
+------------------------+
|                 202301 |
+------------------------+

The year in the result may be different from the year in the date argument for the first and the last week of the year.

Here’s what happens if we move forward a year:

SELECT YEARWEEK('2024-01-01');

Result:

+------------------------+
| YEARWEEK('2024-01-01') |
+------------------------+
|                 202353 |
+------------------------+

The YEARWEEK() function accepts an optional second argument to specify the mode. The actual result you get from this function will depend on the mode being used. If the mode argument is omitted, the value of the default_week_format system variable is used.

See How YEARWEEK() Works in MariaDB for more on the modes and examples of each.