Calculating age from date of birth

Linux and Unix-Like UNIX and Linux Applications
1

MySQL...

Given a column containing people's dates of birth, what's the best way to create a computed column giving their age in years...I can't find a suitable date/time function that'll do the job! :frowning:

2

int((Today - date_of_birth)/10000)
Both dates in yyyymmdd

3

Thanks jgt...

This is what I've ended up with...

SELECT 
    date_format(DOB, '%d/%m/%Y') DOB , 
    convert((date_format(now(),'%Y%m%d') - date_format(DOB, '%Y%m%d'))/10000, unsigned) Age   
FROM
    table

Now, if only I understood why that works! What's the difference between subtracting formatted dates and unformatted dates? And why 10000? :confused:

4

20101012 - 19500101 = 600911
int(600911/10000) = 60
20101012 - 19501011=60001
int(60001/10000)=60
20101012 - 19501013=599999
int(59999/10000)=59

Un-formatted dates are probably in the seconds since an epoch date. Depending on the source, this could be Jan 1 1970 ending in 2037 for 32 bit values.

5

Thanks again jgt. It's obvious when you lay it out like that!:b: