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

by

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

More Related Contents:

  1. If two columns are 1 then make new column
  2. How can I return pivot table output in MySQL?
  3. MySQL – Get row number on select
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. How can I tell when a MySQL table was last updated?
  6. Retrieving the last record in each group – MySQL
  7. Cast from VARCHAR to INT – MySQL
  8. Operand Should Contain 1 Column – MySQL NOT IN
  9. SQL: Creating a Relational table with 2 different auto_increment
  10. How do I add a custom CHECK constraint on a MySQL table?
  11. SQL Query to get column values that correspond with MAX value of another column?
  12. MySQL : transaction within a stored procedure
  13. When to use SELECT … FOR UPDATE?
  14. Setting column values as column names in the SQL query result
  15. Select from one table where not in another
  16. How to select the most recent set of dated records from a mysql table
  17. Second SELECT query if first SELECT returns 0 rows
  18. How to get current date & time in MySQL?
  19. Set value to NULL in MySQL
  20. Laravel join with 3 Tables
  21. SELECT query return 1 row from each group
  22. MySql Single Table, Select last 7 days and include empty rows
  23. mysql SQL: specific item to be first and then to sort the rest of the items
  24. How to regex in a MySQL query
  25. When is a timestamp (auto) updated?
  26. MySQL Group By Hours
  27. RMySQL dbWriteTable with field.types
  28. Syntax error on MERGE statement
  29. MySQL: Computed Column
  30. mySQL query to search all tables within a database for a string?

Leave a Comment