MySQL is a powerful and widely-used relational database management system. One of its many features is the ability to manipulate and extract information from dates. One common task is to extract the year from a date. MySQL provides a function specifically designed for this purpose: the
YEAR()
function. This article will guide you through the usage of the
YEAR()
function, its syntax, and practical examples to help you master the art of extracting years from dates in MySQL.
Understanding the YEAR() Function
The
YEAR()
function in MySQL extracts the year part from a date value. It can be used with both date and datetime data types. If the input value is not a valid date, the function returns
NULL
.
Syntax
The basic syntax of the
YEAR()
function is as follows:
YEAR(date)
date
: This is the date from which you want to extract the year. It can be a string, an integer, or a column name containing a date or datetime value.
Examples
1. Extracting the Year from a Date Column
Suppose you have a table named
employees
with a column
hire_date
that stores the date when each employee was hired. To extract the year from this column for all records, you can use the following query:
SELECT YEAR(hire_date) AS hire_year FROM employees;
This query will return a result set with a single column named
hire_year
, which contains the year of hire for each employee.
2. Extracting the Year from a String
You can also extract the year from a string that represents a date. For example:
SELECT YEAR(‘2021-12-31’) AS extracted_year;
This query will return the year
2021
.
3. Handling Invalid Dates
If you try to extract the year from an invalid date, the
YEAR()
function will return
NULL
. For example:
SELECT YEAR(‘2021-02-30’) AS invalid_date_year;
This query will return
NULL
because February 30th is not a valid date.
4. Extracting the Year from a DATETIME Value
The
YEAR()
function works with datetime values as well. For instance:
SELECT YEAR(CURDATE()) AS current_year;
This query will return the current year based on the system date.
Conclusion
The
YEAR()
function in MySQL is a simple yet powerful tool for extracting the year from date values. By understanding its syntax and practical applications, you can easily incorporate this function into your SQL queries to manipulate and retrieve date information efficiently. Whether you’re working with a date column in a table or extracting the year from a string or datetime value, the
YEAR()
function is a valuable asset in your MySQL toolkit.