Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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?

like image 656
aslingga Avatar asked Oct 13 '11 05:10

aslingga


People also ask

How do I calculate the number of years between two dates in Excel?

In a new cell, type in =DATEDIF(A1,B1,”Y”). The “Y” signifies that you'd like the information reported in years. This will give you the number of years between the two dates. To find the number of months or days between two dates, type into a new cell: =DATEDIF(A1,B1,”M”) for months or =DATEDIF(A1,B1,”D”) for days.

How do you calculate years months and days between two dates?

DATEDIF solution The DATEDIF function is designed to calculate the difference between dates in years, months, and days. There are several variations available (e.g. time in months, time in months ignoring days and years, etc.) and these are set by the "unit" argument in the function.


5 Answers

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

like image 122
Bohemian Avatar answered Oct 12 '22 05:10

Bohemian


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

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

sqlfiddle

just seems easier.

like image 28
pgee70 Avatar answered Oct 12 '22 07:10

pgee70


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)
like image 33
sanmai Avatar answered Oct 12 '22 06:10

sanmai


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

like image 37
Bơ Loong A Nhứi Avatar answered Oct 12 '22 06:10

Bơ Loong A Nhứi


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.

like image 35
stivlo Avatar answered Oct 12 '22 07:10

stivlo