SQL YEAR

What is the SQL YEAR function used for?

Returns the four-digit year component from a DATE, DATETIME, or TIMESTAMP expression.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL YEAR: MySQL, MariaDB, SQL Server, PostgreSQL (via EXTRACT or DATE_PART), Oracle (EXTRACT), SQLite (strftime), Snowflake, BigQuery

SQL YEAR Full Explanation

YEAR is a date-part extraction function that outputs the calendar year (0000-9999) from a valid date or timestamp value. Most commercial databases expose YEAR() as a built-in scalar function, while the SQL standard recommends EXTRACT(YEAR FROM date_expr). Although implementation details vary, the function always returns an integer representing the Gregorian year in the session time zone (unless the column stores timezone-aware data). YEAR is deterministic, index-friendly, and cheap to compute because it involves no I/O beyond reading the date column. Beware of NULL inputs (returns NULL), two-digit year conversions in legacy MySQL modes, and reserved-word conflicts when using YEAR as a column alias without quoting.

SQL YEAR Syntax

-- Vendor-specific form
YEAR(date_expression);

-- Standard SQL form (portable)
EXTRACT(YEAR FROM date_expression);

SQL YEAR Parameters

date_expression (DATE, DATETIME, TIMESTAMP) - Required. The value from which to extract the year.

Example Queries Using SQL YEAR

-- MySQL / SQL Server
SELECT YEAR('2023-10-15') AS yr;                       -- 2023

-- Aggregate sales by year (PostgreSQL)
SELECT DATE_PART('year', order_date) AS sales_year,
       SUM(total_amount) AS revenue
FROM   orders
GROUP  BY sales_year
ORDER  BY sales_year;

-- Filter rows for the current calendar year (SQLite)
SELECT *
FROM   events
WHERE  strftime('%Y', event_ts) = strftime('%Y', 'now');

Expected Output Using SQL YEAR

Each query returns an INT or BIGINT column containing the four-digit year
Aggregate examples group or filter rows based on that integer result

Use Cases with SQL YEAR

Grouping metrics by calendar year
Filtering records to a specific year
Joining fact tables to calendar dimension tables
Building fiscal-year or cohort analyses
Indexing on YEAR(date_col) for faster range scans in MySQL

Common Mistakes with SQL YEAR

Forgetting that YEAR(NULL) returns NULL, causing unexpected filter results
Assuming YEAR returns two digits
Using YEAR on string literals without proper date casting
Treating YEAR as non-reserved and failing to quote when used as a column name
Ignoring timezone offsets when extracting from TIMESTAMP WITH TIME ZONE in Oracle/PostgreSQL

First Introduced In

SQL:2003 (EXTRACT), vendor-specific YEAR existed earlier in MySQL 3.23, SQL Server 2008

Frequently Asked Questions

What is the difference between YEAR() and EXTRACT(YEAR ...)?

YEAR() is a vendor shortcut. EXTRACT(YEAR FROM date_expr) is the portable, ANSI-compliant form.

Can I index on YEAR(date_col) in MySQL?

Yes, functional indexes allow YEAR(date_col). This speeds up queries that filter by year but may increase storage.

Why does YEAR('70-01-01') return 1970 in MySQL?

When the sql_mode is set to ALLOW_INVALID_DATES or NO_ZERO_DATE, MySQL interprets two-digit years using the 1970–2069 window. Cast to DATE with four digits to avoid surprises.

Does YEAR consider time zones?

The function reads the stored value. In TIMESTAMP WITH TIME ZONE types, the engine converts the value to session time zone before extraction, potentially shifting the year at UTC boundaries.