Question 21024 in Mysql Question posted in
The official MySQL documentation can be found here.

How to Get the number of days, months, and years between an employee's date of birth and today's date – Mysql

I tried this but how to calculate for leap years.

SELECT
  FLOOR(DATEDIFF(NOW(), date_of_birth) / 365.25) as years,
  FLOOR(MOD(DATEDIFF(NOW(), date_of_birth), 365.25) / 30.4375) as months,
  MOD(DATEDIFF(NOW(), date_of_birth), 30.4375) as days
FROM employees;

Can anyone please help

Tags:

2

Answers


  1. 0 
    mysql> insert into employees set name = 'Harry Potter', date_of_birth = '1980-07-31';

mysql> SELECT
  TIMESTAMPDIFF(YEAR, date_of_birth, NOW()) AS years,
  TIMESTAMPDIFF(MONTH, date_of_birth, NOW()) AS months,
  TIMESTAMPDIFF(DAY, date_of_birth, NOW()) AS days
FROM employees;

+-------+--------+-------+
| years | months | days  |
+-------+--------+-------+
|    42 |    509 | 15523 |
+-------+--------+-------+

    See https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_timestampdiff

    Re your comment:

    If MySQL date functions didn’t handle leap years, they’d be pretty useless, right? In fact, they do know how to handle leap years.

    mysql> select datediff(now(),'1980-01-30') as diff_with_leap_years;
+----------------------+
| diff_with_leap_years |
+----------------------+
|                15706 |
+----------------------+

mysql> select 365*43 as diff_without_leap_years;
+-------------------------+
| diff_without_leap_years |
+-------------------------+
|                   15695 |
+-------------------------+

    Thus there are 11 leap years between 1980 and today (I am writing this on 2023-01-30).

    Login or Signup to reply.
  2. 0

    For example:

    SELECT @years := TIMESTAMPDIFF(YEAR, date_of_birth, CURRENT_DATE) years,
       @months := TIMESTAMPDIFF(MONTH, date_of_birth + INTERVAL @years YEAR, CURRENT_DATE) months,
       TIMESTAMPDIFF(MONTH, date_of_birth + INTERVAL @years YEAR + INTERVAL @months MONTH, CURRENT_DATE) days
FROM employees;

    Of course you may use according expressions instead of UDVs.

    But there is a problem. The number of days per month vary, so the days amount in years-months-days may differ depends on the calculation direction. My query adds previously calculated years and months to date_of_birth, you may create similar query which substracts years and months from current date.. and the days amount may differ.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search