MySQL YEAR() function

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.



Where dt is a date.

Syntax Diagram:

MySQL YEAR() Function - Syntax Diagram

MySQL Version: 8.0

Pictorial Presentation:

Pictorial Presentation of MySQL YEAR() function

Example: MySQL YEAR() function

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


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


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


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


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()

Follow us on Facebook and Twitter for latest update.