Calculating Age from Date field in DB

[jamdav88]

Hi.

I'm currently trying to setup a form where the admin of the web-site I am making can select various client records from the database by selecting various criteria. I have got it so that the admin can select records relating to the town and gender, but I can't see how to allow for the admin to search by the cirteria of age.

I have a column in the database called Date of Birth, which stores the date as:
YYYY/MM/DD - how would I go about changing this to an actual age by using an SQL statement to retrieve the date turn it into an age and then display the output?

Thanks.

[TheMadScientist]

Hi jamdav88,

Welcome to WebmasterWorld!

Why not allow the selection of an age, convert the age selected into a date range, and then select using that range?
This thread might give you an idea or two about selecting date and ranges using MySQL:

[webmasterworld.com...]

[rocknbil]

I have a column in the database called Date of Birth, which stores the date as:
YYYY/MM/DD -

In particular, follow the previous link and look at post #5 as to why storing a date in a non-standard format is a very bad idea. Second, if you follow that advice, the age is EXTREMELY easy - but I wouldn't store it. The user is X years old today, but what about next year? :-) I'll show you how easy it can be to get the user's age at any moment, without having to store it.

Use a standard mysql date field:

alter table yourtable change dob dob date not null default '0000-00-00';

Why always have a value? Because now, you can always test if it's empty by a single value, 0000-00-00, rather than testing for both NULL as well as empty string ''.

So now when you grab dates, you'll have

2010-01-11

You can order by them, and there are gobs of date_format() functions to turn it exactly into the format you want

select date_format(dob,'%m/%d/%Y') from yourtable
--> 01/11/2010

So let's answer the question, already. :-) mySQL has robust date math functions as well.

Datediff() [dev.mysql.com] expresses the difference between one date and the next in days, which is easy enough to calc years from, but we need to round it down to whole years. Hence the semi-complicated looking select, but it still gives you age in one fell swoop. All we're doing is nesting datediff() inside some math.

select floor(datediff(curdate(),dob)/365);

You can command line test this with your birthday,

select floor(datediff(curdate(),'1984-06-29')/365);
--> 25

(Daughter's birthday, you don't want to know mine, it predates the Epoch . . . .)

so instead of just grabbing the values raw from the DB, you'd do something like this. Note I have **two** selections of dob, one to get the age, one to reformat it.

select id, fname, lname, date_format(dob,'%m/%d/%Y'), floor(datediff(curdate(),dob)/365), username, whatever . . . .

You just have two members for formatted date and age.

Even if you can't wean yourself from

select * from table

You can still perform the conversion on the date. It's just not as efficient.

$dob = $row['dob'];
$age = floor(datediff(curdate(),'$dob')/365);
//Quotes required for literals, no quotes for field names
$query = "select date_format('$dob','%m/%d/%Y')";
// Execute the query, $dob = $row[0]

But none of this is possible without a valid mysql date. So change it, now, you'll be glad you did. :-)

[rocknbil]

Error here, sorry, been ill with the flu . . . . this is the correct select for the very last part:

$query = "select floor(datediff(curdate(),'$dob')/365)";
// Execute the query, $age = $row[0]

[jamdav88]

Hi rocknbil, Thank's for the reply. I'll be trying out your method in the next day or two, thanks alot! Will let you know how I get on.
Thanks,
James.