CODEWITHSUNDEEP
mysqlsql
randombits
randombits

Reputation: 48490

Calculate age with decimals from date of birth

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?

Upvotes: 6

Views: 4944

Answers (4)

hillct
hillct

Reputation: 33

Your best bet is use of DATEDIFF() as follows:

select datediff(NOW(),date("1983-12-02"))/365;

where the quoted date is your date field, so your example would translate to:

select datediff(NOW(),date(dob))/365 from players limit 5;

The result is returned would be the number of years since the birth date to 4 decimal places. If you prefer two decimal places use truncate() as follows:

truncate(datediff(NOW(),date("1983-12-02"))/365,2);

Upvotes: 1

DataGuru
DataGuru

Reputation: 837

There are a couple of issues. you are using 365 as the number of days in a year and ignoring leap years. The easiest way to do this would be 

select datediff( now(), '1981-06-01' )/365.25;

When you use 365.25 it will spread the leap year and you should be fine. MySQL says this value is 33.0157. when you round it off to two decimals you should be fine.

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

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;

Upvotes: 6

Andomar
Andomar

Reputation: 238136

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.

Upvotes: 1

Related Questions