Calculating age in MySQL

Frunkie · #1 (**permalink**) 06-20-07, 07:53

Can someone tell me if there is some function that will take the current year minus a person's date of birth to get their age? If someone could point me towards some info, I would be grateful. I don't see how an aggregate function could do this with the date format.

Thanks..

r937 · #2 (**permalink**) 06-20-07, 08:18

aggregate functions are used to perform a calculation over the values in a column -- you're right, not the thing for this problem

let's say you were born on august 13th, 1971

this is 2007, so 2007 - 1971 = 36, and you are 36 years old today, right?

wrong

mike_bike_kite · #3 (**permalink**) 06-20-07, 08:31

Code:

mysql> select  date_format(now(),'%Y') - substring('19630805',1,4) -
        if (date_format(now(),'%m%d') < substring('19630805',5,4),
                1, 0 ) as age
+------+
| age  |
+------+
|   43 |
+------+

Obviously the 19630805 in this example will need to be a variable (an 8 digit string containing the birthday) and I am mixing types but it's close to what you are after.

It's a terrible thing to suddenly come across your age.

Mike