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

How to calculate the year difference between two dates in MySQL

In database operations, it is often necessary to calculate the time difference between two dates. MySQL provides multiple methods for calculating date differences, including calculating year differences.

Here are some examples:

• 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

To calculate the year difference, you can use the following simple expression:

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

This expression takes leap years into account. Subtraction of date formats ensures that the result is adjusted to the earlier date of the year.

For example, using the given test case:

<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');
SELECT date1, date2,
YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) AS diff_years
FROM so7749639;</code>

The output results are as follows:

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

So using this expression you can effectively get the year difference between two dates in MySQL.

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