Calculating ages from date of birth?

[karlosio]

Im testing out how to calculate someones age from date of birth, i found a site that explained to use this query to calculate someones age:


mysql> select date_format(from_days(datediff("2009-11-01","1980-11-01")), '%Y')+0 as age;


this outputs 28, however as the date I've entered would be someones birthday, making them 29, how could i display that instead? Or is there a better method for calculating someones age?

[CFMaBiSmAd]

http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

[Old Pedant]

Just add 1 before doing the from_days call.

select date_format(from_days(datediff("2009-11-01","1980-11-01")+1), '%Y')+0 as age;

[Old Pedant]

NO NO NO! Just realized that code is *WRONG*!!!

It depends heavily on just when leap year occurs. It can and will be off by one day in some years with some birth dates. Go with the version from the MySQL docs!!

[CFMaBiSmAd]

The definition of a person's age is the difference between the current year and the year of birth, subtract 1 if the birthday has not occurred yet in the current year.

[oesxyl]

Im testing out how to calculate someones age from date of birth, i found a site that explained to use this query to calculate someones age:


mysql> select date_format(from_days(datediff("2009-11-01","1980-11-01")), '%Y')+0 as age;


this outputs 28, however as the date I've entered would be someones birthday, making them 29, how could i display that instead? Or is there a better method for calculating someones age?

mysql> select year("2009-11-01") - year("1980-11-01");


best regards

[Old Pedant]

No, oesxyl, not at all right.

select year("2009-11-01") - year("1980-12-01");
STILL give 29, even though the right answer is 28, since the birth month/day has not occurred yet.

The version in the MySQL docs really *is* the best way.

[oesxyl]

Im testing out how to calculate someones age from date of birth, i found a site that explained to use this query to calculate someones age:


mysql> select date_format(from_days(datediff("2009-11-01","1980-11-01")), '%Y')+0 as age;


this outputs 28, however as the date I've entered would be someones birthday, making them 29, how could i display that instead? Or is there a better method for calculating someones age?

No, oesxyl, not at all right.

select year("2009-11-01") - year("1980-12-01");
STILL give 29, even though the right answer is 28, since the birth month/day has not occurred yet.

The version in the MySQL docs really *is* the best way.
I guess is ok since op want 29 not 28, :)

what do you mean by "the best way"?
best regards

[karlosio]

Thank you for the kind replies, and thank you CFMaBiSmAd for that link. Its exactly what I need :D