YEAR() in SQL
Extracts the year value from a date or timestamp expression.
Syntax
Return type
YEAR() Function Example
What is YEAR() in SQL?
The YEAR() function in SQL extracts the year value from a given date or timestamp expression. It is commonly used for date-based filtering, reporting, and grouping data by year.
This function is supported in multiple databases:
MySQL, PostgreSQL, and SQL Server: Use
YEAR(date_column).Oracle: Uses
EXTRACT(YEAR FROM date_column).SQLite: Uses
strftime('%Y', date_column).
Parameters:
date_expression: Date, datetime, or timestamp value
Example Use Cases:
-- Group sales by year
-- Find records from specific year
-- Calculate years of service
Notes:
Behavior: Returns NULL if input is NULL
Performance Considerations: May prevent index usage if not used carefully
Version Info: Core function available in most DBMS
Deprecated/Recommended Alternatives: Some DBMS prefer EXTRACT()
Error Handling:
Error: Returns NULL for invalid dates
Recommendation: Validate date format before extraction
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
YEAR()
YEAR(date)
Returns NULL
PostgreSQL
EXTRACT(YEAR FROM)
EXTRACT(YEAR FROM date)
Returns NULL
SQL Server
YEAR()
YEAR(date)
Returns NULL
SQLite
strftime('%Y')
strftime('%Y', date)
Returns NULL
BigQuery
EXTRACT(YEAR FROM)
EXTRACT(YEAR FROM date)
Returns NULL
Snowflake
YEAR()
YEAR(date)
Returns NULL
Athena
EXTRACT(YEAR FROM)
EXTRACT(YEAR FROM date)
Returns NULL
