Learning Focus

Use this lesson to understand YEAR with practical syntax and examples.

MySQL YEAR Data Type: Complete Guide

---

What is YEAR?

YEAR is a MySQL data type designed to store 4-digit year values in a compact 1-byte format. It's optimized for scenarios where only the year portion of a date matters.

Key Features

Feature	Details
Format	YYYY (4-digit) or deprecated YY (2-digit)
Range	1901 to 2155 (4-digit), 1970 to 2069 (2-digit)
Storage	1 byte
Default	NULL (if not specified)

---

Declaration & Syntax

Basic Syntax

column_name YEAR

Example

CREATE TABLE cars (
    model VARCHAR(50),
    release_year YEAR  -- 4-digit format (e.g., 2023)
);

Deprecated 2-Digit Syntax (Avoid in MySQL 8.0+)

column_name YEAR(2)  -- Not recommended (e.g., '23' -> 2023)

---

Valid Input Formats

Input Type	Example	Stored Value
4-digit integer	2023	2023
4-digit string	'1999'	1999
2-digit integer	23	2023 (deprecated)
2-digit string	'99'	1999 (deprecated)

---

Practical Examples

Example 1: Valid Inserts

INSERT INTO cars (model, release_year) VALUES
    ('Model X', 2023),
    ('Vintage', 1995);

Example 2: Invalid Inserts

INSERT INTO cars (release_year) VALUES (1899);  -- Error: Year out of range
INSERT INTO cars (release_year) VALUES ('abc');  -- Error: Invalid year value

Example 3: Retrieving Year

SELECT * FROM cars WHERE release_year >= 2000;

---

YEAR vs Other Types

Type	Storage	Range	Use Case
YEAR	1 byte	1901-2155	Year-only storage (e.g., car model years)
SMALLINT	2 bytes	-32768 to 32767	Wider range (e.g., historical years)
DATE	3 bytes	1000-9999	Full date storage

---

Common Functions

Current Year

SELECT YEAR(CURDATE());  -- Returns 2023

Extract Year from Date

SELECT YEAR('2023-10-15');  -- Returns 2023 (but input is a DATE string)

---

Best Practices

1. Always use 4-digit format:

   release_year YEAR  -- Not YEAR(4) or YEAR(2)
   

2. Validate input in your application to avoid out-of-range errors.

3. Use SMALLINT for years before 1901 (e.g., historical data).

4. Avoid arithmetic: YEAR doesn't support INTERVAL operations.

---

Common Issues

1. Year 1900 Problem

INSERT INTO cars (release_year) VALUES (1900);  -- Fails (min is 1901)

2. Deprecated 2-Digit Behavior

-- MySQL 8.0+ warning:
INSERT INTO cars (release_year) VALUES (99);  -- Converts to 1999

3. Invalid String Conversion

INSERT INTO cars (release_year) VALUES ('202X');  -- Error: Not numeric

---

Summary

• YEAR stores 4-digit years (1901-2155) in 1 byte.
• Use for: Model years, publication years, or any year-specific data.
• Avoid for: Historical dates or full date storage.

---

Next Topics

1. DATE and DATETIME
2. Handling Historical Dates
3. Time-Based Calculations

Concept Map

flowchart LR
    A[Schema Context] --> B[YEAR]
    B --> C[Query Pattern]
    C --> D[Validation]
    D --> E[Production Use]

Common Pitfalls

Pitfall	Consequence	Prevention
Executing queries without validating sample rows	Logic errors reach production data or reports	Start with SELECT ... LIMIT 10 and inspect edge cases
Ignoring NULL and duplicate behavior	Aggregations and filters return misleading results	Test with NULL, duplicates, and empty sets explicitly
Using advanced syntax before checking schema	Queries fail due to missing columns/indexes	Verify structure with DESCRIBE table_name; and adapt query design

Quick Reference

CREATE TABLE sample_year (id INT PRIMARY KEY, value YEAR);
DESCRIBE sample_table;
SHOW CREATE TABLE sample_table;

What's Next

• Previous: Date, Time, DateTime - Review the previous lesson to reinforce context.
• Next: Timestamp - Continue to the next concept with incremental complexity.
• Module Overview - Return to this module index and choose another related lesson.