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.

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