Working with dates in SQL can sometimes be tricky, especially when trying to extract specific parts of a date. One of the most commonly used functions in SQL is YEAR(). This function makes it simple to retrieve the year from a given date. In this guide, I’ll explain exactly how YEAR works in SQL, provide examples, and cover various use cases.
What is the YEAR() Function in SQL?
The YEAR() function in SQL is used to extract the year from a date or datetime value. It returns an integer representing the year of the given date.
The syntax is straightforward:
SELECT YEAR(date_column) FROM table_name;Example:
SELECT YEAR('2024-06-15') AS extracted_year;Result:
| extracted_year |
|---|
| 2024 |
Using YEAR() with a Database Table
Let’s assume we have a table named orders with the following structure:
| order_id | customer_name | order_date |
|---|---|---|
| 1 | John Doe | 2023-12-25 |
| 2 | Jane Smith | 2024-05-10 |
If we want to list all orders along with the order year, we can use:
SELECT order_id, customer_name, YEAR(order_date) AS order_year
FROM orders;Expected Output:
| order_id | customer_name | order_year |
|---|---|---|
| 1 | John Doe | 2023 |
| 2 | Jane Smith | 2024 |
Filtering Data Using YEAR()
If you need to filter records based on a specific year, you can use YEAR() in the WHERE clause. For example, to find all orders placed in 2024:
SELECT * FROM orders WHERE YEAR(order_date) = 2024;Common Use Cases for YEAR() in SQL
The YEAR() function is useful in various scenarios, including:
- Creating reports that group data by year.
- Filtering records based on a specific year.
- Calculating differences between years.
- Analyzing trends over time.
Grouping Data by Year
When working with large datasets, it’s often necessary to group records by year. This can be done using GROUP BY with the YEAR() function.
For example, if we want to count the number of orders placed each year:
SELECT YEAR(order_date) AS order_year, COUNT(*) AS total_orders
FROM orders
GROUP BY YEAR(order_date);Sample Output:
| order_year | total_orders |
|---|---|
| 2023 | 1 |
| 2024 | 1 |
Handling NULL Values in YEAR()
What happens if the column contains NULL values? The YEAR() function returns NULL when it encounters a null date value.
To handle this, you can use the COALESCE() function to replace NULL with a default value:
SELECT COALESCE(YEAR(order_date), 'Unknown') AS order_year FROM orders;Performance Considerations of Using YEAR()
Although YEAR() is useful, using it in the WHERE clause can sometimes impact performance. This is because applying a function to a column prevents the database engine from using indexes efficiently.
Instead of:
SELECT * FROM orders WHERE YEAR(order_date) = 2024;Use:
SELECT * FROM orders WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';The second approach ensures that an index on order_date is fully utilized, leading to better query performance.
Conclusion
The YEAR() function in SQL is a powerful tool for working with dates, allowing you to extract and manipulate year values with ease. Whether you’re filtering, grouping, or reporting data based on years, this function provides a simple yet effective solution.
Other interesting article:
How EXTRACT works in SQL? Best EXTRACT examples