MySQL YEAR Function

MySQL YEAR() Function

The YEAR() function in MySQL extracts the year from a given date or datetime expression and returns it as a four-digit number. It is commonly used when working with date-related data to filter or group records by year.

Syntax

YEAR(date_expression)

• date_expression: A valid date or datetime value from which the year is extracted.

Return Value

• Returns the year as a four-digit integer (e.g., 2025).
• Returns NULL if the input is NULL or invalid.

Examples

1. Extracting Year from a Date

SELECT YEAR('2025-01-12') AS year_value;

Output:

year_value
2025

2. Extracting Year from a Datetime

SELECT YEAR('2025-01-12 14:30:00') AS year_value;

Output:

year_value
2025

3. Using YEAR() with NOW()

Extract the current year from the system datetime:

SELECT YEAR(NOW()) AS current_year;

Output:

current_year
2025

4. Filtering Records by Year

Retrieve records for a specific year:

SELECT * 
FROM orders 
WHERE YEAR(order_date) = 2023;

5. Grouping Records by Year

Aggregate sales data by year:

SELECT YEAR(order_date) AS order_year, COUNT(*) AS total_orders
FROM orders
GROUP BY YEAR(order_date);

Output:

order_year | total_orders
-----------|-------------
2023       | 150
2024       | 200

Practical Use Cases

1. Year-Based Filtering:

   • Extract and filter records based on a specific year, such as fetching orders, events, or logs.

2. Annual Reports:

   • Generate summaries or trends grouped by year.

3. Comparing Year Values:

   • Compare years in different columns or variables:

     SELECT * 
     FROM events 
     WHERE YEAR(start_date) = YEAR(end_date);
     

4. Handling User-Input Dates:

   • Extract and use the year from user-provided dates for dynamic filtering.

Considerations

1. Input Format:

   • Ensure the date_expression is in a valid date or datetime format. If the input is invalid, the function will return NULL.

2. Performance:

   • Using YEAR() in a query may prevent the use of indexes on the column, potentially affecting performance. For better performance, consider indexing a derived column that stores the year:

     ALTER TABLE orders ADD COLUMN order_year INT;
     UPDATE orders SET order_year = YEAR(order_date);
     

3. NULL Values:

   • If the date_expression is NULL, the function will return NULL.

Conclusion

The YEAR() function is a simple yet powerful tool for working with year values in MySQL. Whether you're filtering records, generating reports, or comparing dates, the YEAR() function helps streamline date-based operations. With proper indexing and use, it can significantly enhance the efficiency of date-related queries.