How to Efficiently Calculate the Age Difference in Years Between Two Dates Using MySQL?

Calculate year difference using MySQL date

Determining the year difference between two dates is a common task in MySQL. This article provides an efficient single-expression solution that takes leap years into account.

Problem statement:

Given two dates in the following format: 'YYYY-MM-DD', we need to calculate the year difference. For example:

• 2011-07-20 - 2011-07-18 => 0 years
• 2011-07-20 - 2010-07-20 => 1 year
• 2011-06-15 - 2008-04-11 => 3 years
• 2011-06-11 - 2001-10-11 => 9 years

Solution:

<code class="language-sql">YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))</code>

Instructions:

• YEAR(date1) - YEAR(date2) Calculate the year difference.
• DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d') Compares the month and day parts of two dates. The expression evaluates to true (1) if the day of month of date1 is less than the day of month of date2, and false (0) otherwise.
• Subtracting a true or false value (1 or 0) will adjust the result accordingly.

Example:

The following query calculates the year difference for a given test case:

<code class="language-sql">SELECT date1, date2,
YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) AS diff_years
FROM (
    SELECT '2011-07-20' AS date1, '2011-07-18' AS date2 UNION ALL
    SELECT '2011-07-20', '2010-07-20' UNION ALL
    SELECT '2011-06-15', '2008-04-11' UNION ALL
    SELECT '2011-06-11', '2001-10-11' UNION ALL
    SELECT '2007-07-20', '2004-07-20'
) AS dates;</code>

Output:

<code>+------------+------------+------------+
| date1      | date2      | diff_years |
+------------+------------+------------+
| 2011-07-20 | 2011-07-18 |          0 |
| 2011-07-20 | 2010-07-20 |          1 |
| 2011-06-15 | 2008-04-11 |          3 |
| 2011-06-11 | 2001-10-11 |          9 |
| 2007-07-20 | 2004-07-20 |          3 |
+------------+------------+------------+</code>

The above is the detailed content of How to Efficiently Calculate the Age Difference in Years Between Two Dates Using MySQL?. For more information, please follow other related articles on the PHP Chinese website!