How to Extract Year from Date in MySQL

Hi There, In this article you will see how to extract year from the date object in MySQL with the help of the examples. Sometimes we want to apply some condition on the basis of year and year included in the data object, and that is when we get confused.

Through this article, we will see about a MySQL built-in function that is sued to extract the year from the date object.

Why do we require to extract the year from the date in MySQL?

let’s understand this by an example. Suppose we have an employee table and we want to get all those employees who joined in the year 2019 and employee table don’t have any year column however employee table has a column that contains the joining date of the employees.

It will be more complicated to identify all those employees who have joined in a specific year. MySQL offers a built-in function called YEAR() that is sued to extract the year from the date.

MySQL YEAR() Function

The YEAR() function in MySQL is a built-in function that takes a date parameter and extracts the year from that passed date object. The value of retuned value between 1000 and 9999 and it will return NULL if the passed date is NULL.

Syntax

YEAR(date)

Data

I have created a table employee that stored some values inside the id, first_name,last_name, designation, and joining_date columns. As you can see below example.
Throughout this article, I will see use this table to understand MySQL YEAR() functions.

+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | designation              | joining_date |
+----+------------+-----------+--------------------------+--------------+
|  1 | Vishvajit  | Rao       | Python Developer         | 2021-12-23   |
|  2 | Vinay      | Kumar     | Angular Developer        | 2020-08-22   |
|  3 | Minakshi   | Kumary    | SEO Specialist           | 2021-07-20   |
|  4 | John       | Doe       | Data Engineer            | 2020-11-06   |
|  5 | Akash      | Saini     | Junior Data Engineer     | 2022-01-30   |
|  6 | Harshita   | Singh     | Junior Accountant        | 2021-12-25   |
|  7 | Ayush      | Shukla    | Angular Developer        | 2021-08-20   |
|  8 | Abhishek   | Mishra    | Senior Data Engineer     | 2018-05-29   |
|  9 | Vaishanvi  | Mehta     | Senior SEO Specialist    | 2020-02-26   |
| 10 | Mahima     | Chaudhari | Junior Data Scientist    | 2021-03-23   |
| 11 | Arushi     | Shulka    | Junior Data Analyst      | 2021-05-29   |
| 12 | Mahesh     | Kumar     | Senior Python Developer  | 2020-03-22   |
| 13 | Ajay       | Gupta     | Senior Angular Developer | 2021-08-28   |
| 14 | Manish     | Das       | Senior HR Manager        | 2021-07-20   |
| 15 | David      | Russel    | Senior Data Analyst      | 2021-05-21   |
| 16 | Nishant    | Chahal    | Senior Accountant        | 2021-09-24   |
| 17 | Darshan    | Natrajan  | Hiring HR                | 2021-05-20   |
+----+------------+-----------+--------------------------+--------------+

Example: Extracting year from date string

Extracting the year from the date.

SELECT YEAR('2023-01-15') AS 'year';

Output

+------+
| year |
+------+
| 2023 |
+------+
1 row in set (0.16 sec)

Example: Extracting year from date time

In this example, I am extracting the year from the DateTime column.

SELECT YEAR('2008-02-03 22:23:00') AS 'year';

Output

+------+
| year |
+------+
| 2008 |
+------+
1 row in set (0.16 sec)

Example: Extracting Year from Date column in table

As we know that we have a column joining_date column with date type in the above table, Now I am going to extract the year from the joining_date column. Here, I have extracted the year from the joining_date column.

mysql> SELECT
    ->     id,
    ->     first_name,
    ->     last_name,
    ->     designation,
    ->     joining_date,
    ->     YEAR(joining_date) AS 'year of joining'
    -> FROM
    ->     employee;
+----+------------+-----------+--------------------------+--------------+-----------------+
| id | first_name | last_name | designation              | joining_date | year of joining |
+----+------------+-----------+--------------------------+--------------+-----------------+
|  1 | Vishvajit  | Rao       | Python Developer         | 2021-12-23   |            2021 |
|  2 | Vinay      | Kumar     | Angular Developer        | 2020-08-22   |            2020 |
|  3 | Minakshi   | Kumary    | SEO Specialist           | 2021-07-20   |            2021 |
|  4 | John       | Doe       | Data Engineer            | 2020-11-06   |            2020 |
|  5 | Akash      | Saini     | Junior Data Engineer     | 2022-01-30   |            2022 |
|  6 | Harshita   | Singh     | Junior Accountant        | 2021-12-25   |            2021 |
|  7 | Ayush      | Shukla    | Angular Developer        | 2021-08-20   |            2021 |
|  8 | Abhishek   | Mishra    | Senior Data Engineer     | 2018-05-29   |            2018 |
|  9 | Vaishanvi  | Mehta     | Senior SEO Specialist    | 2020-02-26   |            2020 |
| 10 | Mahima     | Chaudhari | Junior Data Scientist    | 2021-03-23   |            2021 |
| 11 | Arushi     | Shulka    | Junior Data Analyst      | 2021-05-29   |            2021 |
| 12 | Mahesh     | Kumar     | Senior Python Developer  | 2020-03-22   |            2020 |
| 13 | Ajay       | Gupta     | Senior Angular Developer | 2021-08-28   |            2021 |
| 14 | Manish     | Das       | Senior HR Manager        | 2021-07-20   |            2021 |
| 15 | David      | Russel    | Senior Data Analyst      | 2021-05-21   |            2021 |
| 16 | Nishant    | Chahal    | Senior Accountant        | 2021-09-24   |            2021 |
| 17 | Darshan    | Natrajan  | Hiring HR                | 2021-05-20   |            2021 |
+----+------------+-----------+--------------------------+--------------+-----------------+
17 rows in set (0.00 sec)

Using YEAR() function with Equal to Operator

In this example, I am about to use the YEAR() function with an equal to the ( = ) operator. Basically, I want to fetch all the employees whose joining_date year is 2021.

mysql> SELECT
    ->     id, first_name, last_name, designation, joining_date
    -> FROM
    ->     employee
    -> WHERE
    ->     YEAR(joining_date) = 2021;
+----+------------+-----------+--------------------------+--------------+
| id | first_name | last_name | designation              | joining_date |
+----+------------+-----------+--------------------------+--------------+
|  1 | Vishvajit  | Rao       | Python Developer         | 2021-12-23   |
|  3 | Minakshi   | Kumary    | SEO Specialist           | 2021-07-20   |
|  6 | Harshita   | Singh     | Junior Accountant        | 2021-12-25   |
|  7 | Ayush      | Shukla    | Angular Developer        | 2021-08-20   |
| 10 | Mahima     | Chaudhari | Junior Data Scientist    | 2021-03-23   |
| 11 | Arushi     | Shulka    | Junior Data Analyst      | 2021-05-29   |
| 13 | Ajay       | Gupta     | Senior Angular Developer | 2021-08-28   |
| 14 | Manish     | Das       | Senior HR Manager        | 2021-07-20   |
| 15 | David      | Russel    | Senior Data Analyst      | 2021-05-21   |
| 16 | Nishant    | Chahal    | Senior Accountant        | 2021-09-24   |
| 17 | Darshan    | Natrajan  | Hiring HR                | 2021-05-20   |
+----+------------+-----------+--------------------------+--------------+
11 rows in set (0.00 sec)

👉 MySQL YEAR() Function Ref:- Click Here

Conclusion

So in this article, we have seen the process of extracting the year from the date in MySQL using the MySQL YEAR() function. This will be very useful when you are working on any real-time projects. You can easily put any condition based on the year with any column that stores the date object.

If you found this article helpful, Please share and keep visiting for further MySQL tutorials.

have a nice day….