How to get the year of a date using the YEAR function in MySQL

How to use the YEAR function in MySQL to get the year of a date

In the MySQL database, we often need to process the year of date data. The YEAR function is a very useful function provided by MySQL, which can extract the year from a date or datetime value. This article will use code examples to introduce the usage of the YEAR function to help readers better understand how to use the YEAR function to obtain the year of a date.

First, we need to create a test table that contains a date type column. You can create a table named test_table and insert some data as an example by following the following code:

CREATE TABLE test_table (
   id INT AUTO_INCREMENT PRIMARY KEY,
   date_value DATE
);

INSERT INTO test_table (date_value) VALUES
   ('2020-01-01'),
   ('2021-02-15'),
   ('2022-08-31'),
   ('2023-12-25');

Now we have created a table containing a date type column and inserted some test data. Next, we will write some queries using the YEAR function to get the year of these date data.

1. Get the year of a date data

First, let us use the YEAR function to get the year of the first data in the test_table table. The query can be executed using the following code:

SELECT YEAR(date_value) AS year_value FROM test_table LIMIT 1;

This will return the following results:

| year_value |
|------------|
| 2020       |

We can see that the YEAR function extracts from the date value 2020-01-01 Out of the year 2020.

2. Get the year of all date data

Next, we will use the YEAR function to get the year of all data in the test_table table. The query can be executed using the following code:

SELECT YEAR(date_value) AS year_value FROM test_table;

This will return the following results:

| year_value |
|------------|
| 2020       |
| 2021       |
| 2022       |
| 2023       |

We can see that the YEAR function extracts the corresponding year from each date value.

3. Use the YEAR function for conditional filtering

In addition to obtaining the year of all date data, we can also use the YEAR function for conditional filtering. For example, we can filter out the data in the test_table table with the year 2022. The query can be executed using the following code:

SELECT * FROM test_table WHERE YEAR(date_value) = 2022;

This will return the following results:

| id | date_value |
|----|------------|
|  3 | 2022-08-31 |

We can see that only the data with the year 2022 meets our conditions.

Through the above example, we can see that the YEAR function is very convenient and practical in obtaining the year of date data. Whether it is to obtain the year of a single date or perform conditional filtering, the YEAR function can help us achieve it easily.

Summary:

The YEAR function is a very useful function in MySQL, which can extract the year from a date type value. In this article, we introduce the usage of the YEAR function through code examples to help readers better understand how to use the YEAR function to obtain the year of a date. I hope this article has been helpful to you in using the YEAR function in MySQL.

The above is the detailed content of How to get the year of a date using the YEAR function in MySQL. For more information, please follow other related articles on the PHP Chinese website!