intro
Given a date, the year is often one of the most important pieces of information. For example, it plays a key role in SQL data aggregation, such as retrieving all orders made in a specific year. This is why knowing how to extract the year from a date in SQL is essential.
Let’s discover some extract year from date SQL approaches!
What Is the Date Format in SQL?
SQL supports several date formats, depending on the database system. For example, MySQL allows certain literal formats, while PostgreSQL accepts others. Additionally, there are multiple SQL date data types, each with its own characteristics.
To promote interoperability, most databases follow the standard YYYY-MM-DD (ISO 8601) format. However, each database engine has its own default formats and functions for handling dates. Thus, always refer to the official documentation for more information:
No matter the format, the goal of this article is to learn how to extract the year from a date in SQL!
How to Retrieve the Year from a Date in SQL
Assuming a date follows the ISO 8601 literal format, the year appears as a four-digit number. Specifically, your goal is to extract the YYYY portion from a YYYY-MM-DD date. If you are dealing with a string, you might be tempted to extract all characters before the first -.
The problem is that some databases support string formats like YY-MM-DD or other variations.
Two-digit years—though discouraged by most databases but still supported, together with other less popular formats, make the process much trickier. This is why treating dates as plain strings is not ideal. Instead, it is best to store or cast your date values as DATE types and use the database's built-in functions to extract the year. That approach is far more reliable.
Note: The queries below will be executed in DbVisualizer, a top-rated, visual, feature-rich database client that supports 50+ databases.
Now, let’s explore extract year from date SQL approaches across popular databases:
MySQL: Extract Year from Date
The easiest way to extract the year from a MySQL date is by using the YEAR() function. When given a DATE value as input, it returns:
This is how to use it:
1
SELECT YEAR("2025-02-16");
In the case of a two-digit year (YY-MM-DD), MySQL assumes:
For example:
1
SELECT YEAR("69-02-16");
produces:
1
2069
Instead, the following query:
1
SELECT YEAR("70-02-16");
returns:
1
1970
Verify that by executing the queries in DbVisualizer:
PostgreSQL: Extract Year from Date
In PostgreSQL, there are two ways to extract the year from a date:
Both functions work the same way, making them interchangeable.
Use EXTRACT() to extract a year from a date as below:
1
SELECT EXTRACT(YEAR FROM DATE '2025-02-16');
Similarly, use DATE_PART() as follows:
1
SELECT DATE_PART('year', DATE '2025-02-16');
The result for both queries is 2025:
1
2025
You can verify that in DbVisualizer by running the functions and checking the output:
Note: PostgreSQL does not support two-digit years in date literals.
SQL Server: Extract Year from Date
The most common SQL Server extract year from date methods are:
In SQL Server, YEAR() returns an integer representing the year of the given date. Use it as follows:
1
SELECT YEAR('2025-02-16');
Alternatively, you can use DATEPART() with the YEAR argument:
1
SELECT DATEPART(YEAR, '2025-02-16');
In both cases, the result will be:
1
2025
Prove that in DbVisualizer:
Oracle SQL: Extract Year from Date
The best way to extract the year from a date in SQL using Oracle is the EXTRACT() method. This works just like the PostgreSQL EXTRACT() function seen earier:
1
SELECT EXTRACT(YEAR FROM DATE '2025-02-16') FROM dual;
Again, the result is:
1
2025
Check that in DbVisualizer:
Extract Year from Date SQL Best Practices
These are the key considerations to keep in mind when extracting years from dates in SQL:
Conclusion
In this blog post, you learned how to extract years from dates in SQL. As shown in the examples, the process becomes easier with a multi-database client like DbVisualizer.
DbVisualizer offers powerful capabilities, including visual query execution, data exploration, and table discovery. It also provides advanced features like SQL formatting and ERD-style schema visualization. Try DbVisualizer for free today!
FAQ
How to extract the year from a date in SQL Server?
Compared to the two methods presented earlier, another approach is to use the FORMAT() function as follows:
1
SELECT FORMAT(CAST('2025-02-16' AS DATE), 'yyyy') AS extracted_year;
The result will be:
1
2025
However, this method is not recommended for performance-critical queries because FORMAT() is slower than YEAR() or DATEPART(), as it involves string conversion. Also, it only works with DATE, DATETIME, or DATETIME2 inputs. Learn more in our guide on SQL Server date format.
What is the difference between EXTRACT() and DATE_PART() in PostgreSQL?
In PostgreSQL, EXTRACT() and DATE_PART() serve the same purpose—they extract specific components (e.g., year, month, day) from a supported date type. While their syntax is slightly different, they produce the same result.
Why use a visual database client?
Using a visual database client like DbVisualizer allows you to work with data visually, regardless of the database you are using. That makes tasks like extracting the year from a date in SQL much easier, as you get instant feedback on the results. That is just one of the many powerful features DbVisualizer offers. Grab a 21-day free trial today!
