How to get the difference in years from two different dates?

I want to get the difference in years from two different dates using MySQL database.

for example:

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

How about the SQL syntax? Is there any built in function from MySQL to produce the result?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Here’s the expression that also caters for leap years:

YEAR(date1) - YEAR(date2) - (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d'))

This works because the expression (DATE_FORMAT(date1, '%m%d') < DATE_FORMAT(date2, '%m%d')) is true if date1 is “earlier in the year” than date2 and because in mysql, true = 1 and false = 0, so the adjustment is simply a matter of subtracting the “truth” of the comparison.

This gives the correct values for your test cases, except for test #3 – I think it should be “3” to be consistent with test #1:

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;

Output:

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

See SQLFiddle

Method 2

I like the solution by Bohemian, but what about using timestampdiff

select date1, date2,timestampdiff(YEAR,date2,date1) from so7749639

sqlfiddle

just seems easier.

Method 3

mysql> SELECT FLOOR(DATEDIFF('2011-06-11','2001-10-11')/365);
+------------------------------------------------+
| FLOOR(DATEDIFF('2011-06-11','2001-10-11')/365) |
+------------------------------------------------+
|                                              9 |
+------------------------------------------------+
1 row in set (0.00 sec)

DATEDIFF() returns difference in days between two dates. This does not specifically take leap years into account but it may work in such cases:

mysql> SELECT FLOOR(DATEDIFF('2007-07-11','2004-07-11')/365);
+------------------------------------------------+
| FLOOR(DATEDIFF('2007-07-11','2004-07-11')/365) |
+------------------------------------------------+
|                                              3 |
+------------------------------------------------+
1 row in set (0.00 sec)

Method 4

Simply by: SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table.

Method 5

you could just use

SELECT ROUND((TO_DAYS(date2) - TO_DAYS(date1)) / 365) ...

Also wrap it with ABS() if you want always a positive number, no matter which date precedes the other.

With ROUND(), 0.6 years will be considered 1 year, if instead you want to count only the full years, you can use FLOOR(). In this case 0.6 year will be considered 0 years, and 1.9 years will be considered 1 year.

Method 6

Number of years between date1 and date2:

IF((YEAR(date2) - YEAR(date1)) > 0, (YEAR(date2) - YEAR(date1)) - (MID(date2, 6, 5) < 
MID(date1, 6, 5)), IF((YEAR(date2) - YEAR(date1)) < 0, (YEAR(date2) - YEAR(date1)) + 
(MID(date1, 6, 5) < MID(date2, 6, 5)), (YEAR(date2) - YEAR(date1))))

Now for some comments about these.

1. These results return integer number of years, months, and days. They are “floored.” Thus, 1.4 days would display as 1 day, and 13.9 years would display as 13 years. Likewise, -1.4 years would display as -1 year, and -13.9 months would display as -13 months.
2. Note that I use boolean expressions in many cases. Because boolean expressions evaluate to 0 or 1, I can use them to subtract or add 1 from the total based on a condition.

Method 7

This works well, even taking in account for leap years:

select floor((cast(date_format('2016-02-14','%Y%m%d') as int) - cast(date_format('1966-02-15','%Y%m%d') as int)/10000);

Keep the floor as a decimal will be incorrect most of the time.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0