How to use the MySQL YEAR() function

In this article, we will learn how to use the YEAR() function in MySQL. The YEAR() function returns the year for a given date or datetime expression. This function can be useful for performing calculations or comparisons based on the year.

Syntax

The syntax of the YEAR() function is as follows:

YEAR(date)

The date parameter can be any valid date or datetime expression. If the date is not a valid date, the function returns NULL.

Examples

Let’s see some examples of how to use the YEAR() function in MySQL.

Example 1: Get the current year

The following query returns the current year, using the CURDATE() function to get the current date.

SELECT YEAR(CURDATE()) AS current_year;

The output is:

+--------------+
| current_year |
+--------------+
|         2023 |
+--------------+

This means that the current year is 2023.

Example 2: Get the year from a specific date

The following query returns the year from the date ‘2023-12-31’, which is a Saturday.

SELECT YEAR('2023-12-31') AS year;

The output is:

+------+
| year |
+------+
| 2023 |
+------+

This means that the date ‘2023-12-31’ belongs to the year 2023.

Example 3: Get the number of days in a year

We can use the YEAR() function to get the number of days in a year. For example, the following query returns the number of days in the year 2023.

SELECT DATEDIFF(DATE(CONCAT(YEAR('2023-01-01'), '-12-31')), DATE(CONCAT(YEAR('2023-01-01'), '-01-01'))) + 1 AS days_in_year;

The output is:

+--------------+
| days_in_year |
+--------------+
|          365 |
+--------------+

This means that the year 2023 has 365 days.

Example 4: Get the number of leap years between two dates

We can use the YEAR() function to get the number of leap years between two dates. A leap year is a year that has 366 days, and occurs every four years, except if the year is divisible by 100 and not divisible by 400. For example, the following query returns the number of leap years between ‘2000-01-01’ and ‘2023-12-31’.

SELECT SUM(CASE WHEN DATEDIFF(DATE(CONCAT(YEAR(date), '-12-31')), DATE(CONCAT(YEAR(date), '-01-01'))) + 1 = 366 THEN 1 ELSE 0 END) AS leap_years
FROM (
  SELECT DATE('2000-01-01') + INTERVAL n YEAR AS date
  FROM (
    SELECT a.N + b.N * 10 + c.N * 100 AS n
    FROM (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS a
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS b
    CROSS JOIN (
      SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
      UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
      UNION ALL SELECT 8 UNION ALL SELECT 9
    ) AS c
  ) AS t
  WHERE n BETWEEN 0 AND 23
) AS t;

The output is:

+------------+
| leap_years |
+------------+
|          6 |
+------------+

This means that there are 6 leap years between ‘2000-01-01’ and ‘2023-12-31’, which are 2000, 2004, 2008, 2012, 2016, and 2020.

Related Functions

Some of the related functions that can be used with the YEAR() function are:

• MONTH(): Returns the month for a given date or datetime expression, where January is 1 and December is 12.
• DAY(): Returns the day of the month for a given date or datetime expression, between 1 and 31.
• HOUR(): Returns the hour for a given time or datetime expression, between 0 and 23.
• MINUTE(): Returns the minute for a given time or datetime expression, between 0 and 59.
• SECOND(): Returns the second for a given time or datetime expression, between 0 and 59.

Conclusion

In this article, we learned how to use the YEAR() function in MySQL. We saw some examples of how to get the year, the number of days, and the number of leap years for a given date or year. We also learned about some related functions that can be used with the YEAR() function.