How to Accurately Calculate the Year Difference Between Two Dates in MySQL?

Calculating Year Differences Between Dates in MySQL

This guide demonstrates a precise method for calculating the year difference between two dates within a MySQL database. The following SQL expression provides an accurate calculation, handling potential leap year complexities:

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

This expression works as follows:

1. Year Difference: YEAR(date1) - YEAR(date2) calculates the initial year difference.

2. Month/Day Comparison: DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d') compares the month and day components of both dates. This comparison determines if date1 falls earlier in the year than date2.

3. Leap Year Adjustment: MySQL interprets the comparison result as 1 (true) or 0 (false). Subtracting this result from the initial year difference ensures accurate calculation, adjusting for instances where date1 is chronologically earlier within the same year as date2.

Example:

Let's illustrate with sample data:

<code class="language-sql">CREATE TABLE so7749639 (date1 DATE, date2 DATE);
INSERT INTO so7749639 VALUES
('2011-07-20', '2011-07-18'),
('2011-07-20', '2010-07-20'),
('2011-06-15', '2008-04-11'),
('2011-06-11', '2001-10-11'),
('2007-07-20', '2004-07-20');</code>

The query:

<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 so7749639;</code>

Yields the correct year differences:

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

This approach guarantees accurate year difference calculations regardless of the specific dates involved.

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