SQL YEAR

The SQL YEAR function is used to extract the year from a given date or datetime value. This function is commonly used in date calculations, filtering data by year, and performing aggregations based on years.

In this tutorial, we will explore the SQL YEAR function, its syntax, and practical examples to demonstrate its usage.

Syntax of SQL YEAR Function

The basic syntax of the YEAR function in SQL is:

</>
Copy
SELECT YEAR(date_column) AS extracted_year
FROM table_name;

You can also use YEAR with a direct date value:

</>
Copy
SELECT YEAR('2025-07-15') AS extracted_year;

This will return 2025 as the extracted year.

Step-by-Step Examples Using SQL YEAR Function

1. Extracting Year from a Date Column

Let’s create a employees table and use the YEAR function to extract the year from the joining date of employees.

</>
Copy
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    joining_date DATE
);

Insert sample data:

</>
Copy
INSERT INTO employees (name, joining_date)
VALUES 
('Arjun', '2018-05-23'),
('Ram', '2020-08-17'),
('Priya', '2022-11-09');

Now, let’s extract the year from the joining_date column:

</>
Copy
SELECT name, joining_date, YEAR(joining_date) AS joining_year
FROM employees;

Explanation:

  • The YEAR(joining_date) function extracts only the year from the joining_date column.
  • The result includes the employee’s name, original joining date, and extracted year.

2. Filtering Data Based on Year

We can use the YEAR function in the WHERE clause to filter records based on a specific year. For example, let’s find employees who joined in the year 2020:

</>
Copy
SELECT name, joining_date
FROM employees
WHERE YEAR(joining_date) = 2020;

Explanation:

  • The YEAR(joining_date) function extracts the year from each row.
  • The WHERE condition filters only employees who joined in 2020.
  • This query will return details for Ram as he joined in 2020.

Conclusion

The SQL YEAR function is used for extracting and filtering data based on years. In this tutorial, we covered:

  1. How to extract the year from a date column.
  2. Using YEAR with a direct date value.
  3. Filtering records based on specific years.