What is Year Type in MySQL: Explanation and Usage

In MySQL, the YEAR data type stores year values in a 4-digit format (YYYY). It is used to represent years from 1901 to 2155 efficiently, saving space compared to storing full dates.

How It Works

The YEAR type in MySQL is designed to store just the year part of a date, like 2024, without the month or day. Think of it like a calendar that only shows the year number on a page, ignoring the rest of the date details.

It stores years as 4-digit numbers ranging from 1901 to 2155. Internally, it uses 1 byte of storage, which is smaller than storing a full date. This makes it efficient when you only need to track the year, such as a birth year or a product's release year.

When you insert a year value, MySQL accepts either a 2-digit or 4-digit number but stores it as 4 digits. For example, inserting '99' will be stored as 1999. This helps keep the data consistent and easy to read.

Example

This example shows how to create a table with a YEAR column and insert some year values.

sql

CREATE TABLE movies (
  id INT PRIMARY KEY,
  title VARCHAR(100),
  release_year YEAR
);

INSERT INTO movies (id, title, release_year) VALUES
(1, 'The Matrix', 1999),
(2, 'Inception', 2010),
(3, 'Back to the Future', 1985);

SELECT * FROM movies;

Output

id | title              | release_year
---|--------------------|-------------
1  | The Matrix         | 1999
2  | Inception          | 2010
3  | Back to the Future | 1985

When to Use

Use the YEAR type when you only need to store the year part of a date without month or day details. This is common for:

• Storing birth years in user profiles.
• Tracking product release years.
• Recording event years where exact dates are not important.

It saves space and makes queries simpler when full date precision is unnecessary.

Key Points

• YEAR stores only the year as a 4-digit number.
• Valid range is from 1901 to 2155.
• Uses 1 byte of storage, efficient for year-only data.
• Accepts 2-digit or 4-digit input but stores as 4-digit.
• Ideal for data where month and day are not needed.

Key Takeaways

The YEAR type stores only the year part of a date in 4-digit format.

It uses less storage than full date types, saving space.

Valid years range from 1901 to 2155 in MySQL.

Use YEAR when you don't need month or day details.

MySQL converts 2-digit years to 4-digit automatically.