How the YEAR() function works in Mariadb?

The YEAR() function in MariaDB is a date function that extracts the year part from a given date.

Posted on

The YEAR() function in MariaDB is a date function that extracts the year part from a given date. It returns the year as a four-digit number.

Syntax

The syntax for the MariaDB YEAR() function is as follows:

YEAR(date)

Where date is a date or datetime expression from which you want to extract the year.

Examples

Example 1: Basic Usage of YEAR()

This example demonstrates how to extract the year from a specific date.

SELECT YEAR('2024-03-10') AS year;

Below is the output for the statement:

+------+
| year |
+------+
| 2024 |
+------+

This indicates that the year extracted from the date ‘2024-03-10’ is 2024.

Example 2: Using YEAR() with a Table

First, let’s create a table with some dates and then use the YEAR() function to find out the years.

DROP TABLE IF EXISTS example_dates;
CREATE TABLE example_dates (date_value DATE);
INSERT INTO example_dates VALUES ('2024-01-01'), ('2025-12-31'), ('2026-07-04');

Now, let’s query the table:

SELECT date_value, YEAR(date_value) AS year FROM example_dates;

Below is the output for the statement:

+------------+------+
| date_value | year |
+------------+------+
| 2024-01-01 | 2024 |
| 2025-12-31 | 2025 |
| 2026-07-04 | 2026 |
+------------+------+

The output shows the year for each date in the table.

Example 3: YEAR() with Current Date

To find the year of the current date:

SELECT YEAR(CURDATE()) AS current_year;

Below is the output for the statement:

+--------------+
| current_year |
+--------------+
|         2024 |
+--------------+

The output will show the current year.

Example 4: YEAR() in a WHERE Clause

You can also use the YEAR() function in a WHERE clause to filter results based on the year.

SELECT * FROM example_dates WHERE YEAR(date_value) = 2025;

Below is the output for the statement:

+------------+
| date_value |
+------------+
| 2025-12-31 |
+------------+

This will return rows where the date_value is in the year 2025.

Example 5: YEAR() with ORDER BY

Sorting records by the year:

SELECT * FROM example_dates ORDER BY YEAR(date_value);

Below is the output for the statement:

+------------+
| date_value |
+------------+
| 2024-01-01 |
| 2025-12-31 |
| 2026-07-04 |
+------------+

This will sort the results starting with the earliest year.

Below are a few functions related to the MariaDB YEAR() function:

  • MariaDB MONTH() function returns the month from the date passed.
  • MariaDB DAY() function returns the day of the month from the date passed.
  • MariaDB DATE_FORMAT() function formats the date value according to the format string.

Conclusion

The YEAR() function is a straightforward and efficient way to extract the year from a date in MariaDB. It is useful for reporting, data analysis, and any situation where the year component of a date is required. With the examples provided, you should have a comprehensive understanding of how to use the YEAR() function in your database queries.