MySql get difference between two dates in years and months (YYYY-MM)
This can be used for calculating experience of an employee between two dates. For example : if we have experiencetable table with following columns id , joinedOn , leftOn
+----+------------+------------+
| id | joinedOn | leftOn |
+----+------------+------------+
| 1 | 1996-04-01 | 2018-02-01 |
| 2 | 2006-02-28 | 2009-01-06 |
| 3 | 2020-01-19 | 2020-06-01 |
| 4 | 2002-06-12 | 2019-01-06 |
+----+------------+------------+To get experience in years and months, you can run following query
SELECT
id,
joinedOn,
leftOn,
CONCAT(
FLOOR(
TIMESTAMPDIFF(YEAR, joinedOn, leftOn)
),
" years(s) ",
FLOOR(
TIMESTAMPDIFF(MONTH, joinedOn, leftOn) % 12
),
" month(s)"
) AS "Experience"
FROM
experiencetable;
You will be getting following output
+----+------------+------------+--------------------------+
| id | joinedOn | leftOn | Experience |
+----+------------+------------+--------------------------+
| 1 | 1996-04-01 | 2018-02-01 | 21 years(s) 10 month(s) |
| 2 | 2006-02-28 | 2009-01-06 | 2 years(s) 10 month(s) |
| 3 | 2020-01-19 | 2020-06-01 | 0 years(s) 4 month(s) |
| 4 | 2002-06-12 | 2019-01-06 | 16 years(s) 6 month(s) |
+----+------------+------------+--------------------------+ALTERNATE TITLES