get an age from a D.O.B field

Question

I need to calculate the age of a "customer" from their date of birth.

have tried to use:

DATEDIFF(year,customer.dob,"2010-01-01");

but it doesnt seem to like it.

Any ideas, I KNOW its going to be something simple!?

Thanks

Answer 1

SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(dob, '%Y') - (DATE_FORMAT(NOW(), '00-%m-%d') < DATE_FORMAT(dob, '00-%m-%d')) AS age

Answer 2

A few ways:

select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y’)+0 AS age

Hope this helps you

Answer 3

Bryan Denny's answer is more correct than the accepted answer (I wasn't sure how to put this in somewhere other than a new answer; this is my first time on StackOverflow).

Marcos' first attempt:

select DATEDIFF(customer.dob, '2010-01-01') / 365.25 as age

will firstly yield a negative result (the arguments to DATEDIFF are in the wrong order), and secondly will produce inaccurate results for some dates, e.g.:

SELECT DATEDIFF('2010-05-11','1984-05-11') / 365.25 AS age

produces the result:

25.9986

You can't just simply always round up, because that will also cause inaccurate results for other inputs.

Marcos' second attempt:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(customer.dob,'2010-01-01')), ‘%Y’)+0 AS age

Again, the arguments are in the wrong order, except this time instead of just producing a negative number, the FROM_DAYS() function does not work correctly with negative input. Secondly, if we look closer at the output of the FROM_DAYS() function:

select from_days(datediff('2010-09-16','1984-05-11'));

The result of the above is:

0026-05-08

which is literally "8th of May, Year 26 (after 0)". Keep in mind that for datetime types, there is no month "0", so if you wanted to use this format to measure a date interval with months included, you'd have to subtract 1 from the month. Similarly, with the day component, there is no "0", so the result is not what you'd expect for this problem when the date happens to be the birthday:

select from_days(datediff('2010-05-11','1984-05-11'));

produces:

0025-12-31

which if we shorten using Marcos' date formatting gives us "25", which is an incorrect calculation of age.

Bryan Denny's answer is correct in all these edge cases. His formula is quite clever:

SELECT DATE_FORMAT(reference, '%Y') - DATE_FORMAT(birthdate, '%Y') - (DATE_FORMAT(reference, '00-%m-%d') < DATE_FORMAT(birthdate, '00-%m-%d')) AS age

The first part calculates the difference in years between the two dates. So if we take "2010" and "1984" as reference and birthdate respectively, the result is "26". The second part then calculates essentially "Does the birthdate month and day occur after the reference month and day?" If it does, it "hasn't happened yet", so we need to subtract an additional 1 from the year difference to make up for this. This is taken care of by the result of the < comparison, which returns 1 if true and 0 if false.

So, full examples:

1)

Reference date: 2010-05-10;
Birthdate: 1984-05-11

Year difference = 2010 - 1984 = 26
Month and day comparison: May 10th < May 11th? Yes => subtract an additional year
Calculated age: 25 years

2)

Reference date: 2010-05-11;
Birthdate: 1984-05-11

Year difference = 2010 - 1984 = 26
Month and day comparison: May 11th < May 11th? No => subtract 0
Calculated age: 26 years

I hope this makes things clearer for people!