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-2开发者_如何转开发0 - 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?

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

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

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

sqlfiddle

just seems easier.

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

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)

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.

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.

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.