MySQL YEAR Data Type: Range, YEAR(4) Deprecation and Examples

The MySQL YEAR data type stores a calendar year, not a complete date. It uses one byte of storage and supports years 1901 through 2155, plus the special zero value displayed as 0000. Use it when only a year matters, such as a publication or model year. Use a DATE or DATETIME column if you need the month and day as well.

Syntax and valid values

Declare the type without a display-width suffix:

event_year YEAR

Older tutorials may use YEAR(4). That explicit width is deprecated since MySQL 8.0.19; it means the same thing as YEAR and does not enable wider years or zero-padding options. Prefer YEAR for new schemas. YEAR(2) is not supported in MySQL 8.0 and later.

For the exact accepted input formats, see the MySQL YEAR reference.

Create a table with a YEAR column

The following example stores years as values rather than strings containing entire dates:

CREATE TABLE events (
    event_id INT PRIMARY KEY,
    event_year YEAR NOT NULL
);

INSERT INTO events (event_id, event_year)
VALUES (1, 2024), (2, 2030);

SELECT event_id, event_year
FROM events
ORDER BY event_id;

event_id  event_year
--------  ----------
1         2024
2         2030

A year beyond 2155 is outside the supported range. With strict SQL mode enabled, inserting an invalid year raises an error; without strict mode, MySQL can replace invalid values with 0000 and issue a warning. Do not disable strict mode just to accept invalid data.

Two-digit years and the zero-year trap

MySQL can interpret two-digit YEAR inputs, but the rules are easy to misunderstand:

• Values from 1 through 69 map to 2001 through 2069.
• Values from 70 through 99 map to 1970 through 1999.
• Numeric 0 stores 0000, but the string '0' or '00' represents 2000.

This is why four-digit years are safer for imports. If 0000 is not meaningful in your application, validate it explicitly before inserting or accept NULL when your schema allows unknown years.

YEAR data type versus YEAR() function

YEAR is the column type. YEAR(date_expression) extracts the year from a date or datetime expression:

SELECT YEAR('2024-08-09') AS extracted_year;

extracted_year
--------------
2024

If you already have a full date, keep it as DATE and extract or filter its year when needed. A YEAR column alone cannot tell you which day or month an event occurred.

Filter dates in a specific year

For an indexed DATE or DATETIME column, prefer a half-open range:

SELECT order_id, ordered_at
FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2026-01-01';

Compared with WHERE YEAR(ordered_at) = 2025, the range predicate usually gives a normal index on ordered_at a better opportunity to support the filter. The right choice still depends on your indexes and the optimizer’s execution plan. For a table with a native YEAR column, use WHERE event_year = 2025.

Calculate completed years correctly

Subtracting calendar-year numbers does not always give completed years of service or age. For example, between March 15, 2018 and January 1, 2026, only seven full anniversaries have passed:

SELECT
    YEAR('2026-01-01') - YEAR('2018-03-15') AS calendar_year_difference,
    TIMESTAMPDIFF(YEAR, '2018-03-15', '2026-01-01') AS completed_years;

calendar_year_difference  completed_years
------------------------  ---------------
8                         7

The fixed dates make this result reproducible instead of allowing examples that silently become incorrect every January.

Frequently asked questions

Is YEAR(4) still needed?

No. Use YEAR without a width. YEAR(4) is deprecated, and both forms have the same meaning in versions that accept them.

Can YEAR store 1800 or 2200?

No. The supported normal range is 1901–2155. For a broader range, consider an appropriate integer type and validate its business meaning.

Is YEAR the same as DATE or the YEAR() function?

No. YEAR stores only a year; DATE stores a full calendar date; YEAR() extracts a year from a date/time expression.

For version-specific details, consult the MySQL 8.4 YEAR type documentation and MySQL strict SQL modes.