MySQL YEAR() function

Last update on September 08 2023 11:33:05 (UTC/GMT +8 hours)

YEAR() function

MySQL YEAR() returns the year for a given date. The return value is in the range of 1000 to 9999 or 0 for 'zero' date.

This function is useful in -

It's particularly valuable for businesses that operate on an annual cycle, allowing them to generate reports, track progress, and set goals on a yearly basis.
YEAR() helps in generating reports based on the company's specific fiscal year that don't align with the calendar year.
It allows you to segment or categorize data by year, which is useful for trend analysis, identifying patterns, and making year-over-year comparisons.
YEAR() is essential for budgeting and forecasting, as it allows businesses to plan and allocate resources for the upcoming year.
For historical data sets, YEAR() helps in extracting the year from date fields, allowing you to analyze trends and patterns over extended periods.
It's used to calculate the age of a person or the age of an entity based on their birth year.
YEAR() is used in applications to remind users of anniversaries, such as membership anniversaries or subscription renewals.

Syntax:

YEAR(dt)

Where dt is a date.

Syntax Diagram:

MySQL Version: 8.0

Pictorial Presentation:

Example: MySQL YEAR() function

The following statement will return the year part of the specified date 2009-05-19.

Code:

SELECT YEAR('2009-05-19');

Output:

mysql> SELECT YEAR('2009-05-19');
+--------------------+
| YEAR('2009-05-19') |
+--------------------+
|               2009 | 
+--------------------+
1 row in set (0.00 sec)

Example: YEAR() function using table

The following statement will retrieve the columns 'book_name', 'dt_of_pub' and year of publication from book_mast table for those rows whose year of 'dt_of_pub' are after 2003.

Sample table: book_mast

Code:

SELECT book_name,dt_of_pub,YEAR(dt_of_pub)
FROM book_mast
WHERE YEAR(dt_of_pub)>2003;

Output:

mysql> SELECT book_name,dt_of_pub,YEAR(dt_of_pub)
    -> FROM book_mast
    -> WHERE YEAR(dt_of_pub)>2003; 
+----------------------------------+------------+-----------------+
| book_name                        | dt_of_pub  | YEAR(dt_of_pub) |
+----------------------------------+------------+-----------------+
| Transfer  of Heat and Mass       | 2004-02-16 |            2004 | 
| Advanced 3d Graphics             | 2004-02-16 |            2004 | 
| Mental Health Nursing            | 2004-02-10 |            2004 | 
| The Experimental Analysis of Cat | 2007-06-09 |            2007 | 
| The Nature  of World             | 2005-12-20 |            2005 | 
+----------------------------------+------------+-----------------+
5 rows in set (0.06 sec)

Video Presentation:

All Date and Time Functions:

Click here to see the MySQL Date and time functions.

Previous: WEEK OF YEAR()
Next: YEARWEEK()