Calculate age with decimals from date of birth

Question

I have a dob field in my MySQL table that's of type date. Just a small, trivial example is something like this:

mysql> select dob from players limit 5;
+------------+
| dob        |
+------------+
| 1983-12-02 |
| 1979-01-01 |
| 1989-05-11 |
| 1976-03-24 |
| 1989-09-12 |
+------------+

I am trying to calculate ages with decimal points by using today's date. So technically if your birthday is June 1, 1981 or 1981-06-01, that makes you 33 and today is June 7.. you'd be 33.(6/365) or 33.02 years old. What's the easiest way to calculate this using SQL?

Answer 1

Usually DOB calculation is pretty easy in mysql when you want to calculate the years without any fraction something as

mysql> select timestampdiff(YEAR,'1981-06-01',now());
+----------------------------------------+
| timestampdiff(YEAR,'1981-06-01',now()) |
+----------------------------------------+
|                                     33 |
+----------------------------------------+

But since you need the fraction also then this should do the trick

mysql> select format(datediff(curdate(),'1981-06-01') / 365.25,2);
+-----------------------------------------------------+
| format(datediff(curdate(),'1981-06-01') / 365.25,2) |
+-----------------------------------------------------+
| 33.02                                               |
+-----------------------------------------------------+

Year is considered as 365.25 days.

So in your case you may have the query as

select 
format(datediff(curdate(),dob) / 365.25,2) as dob 
from players limit 5;

Answer 2

You can use the to_days function to calculate the days between the year zero and someone's birthday. Then subtract from today that number of days. That should give you the birthday as if someone was born in the year zero:

select  year(subdate(now(), to_days(dob)))

Example at SQL Fiddle.