I want to get the difference in years from two different dates using MySQL database.
for example:
How about the SQL syntax? Is there any built in function from MySQL to produce the result?
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.
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.
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.
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)
Simply by: SELECT TIMESTAMPDIFF(YEAR, date1, date2) AS difference FROM table
.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With