Check if MySQL DATE field (birthdate) is within age range?

[Helmet]

I'm scratching my head wondering how to do this. I'd like to check if a member is between a variable age range, say 20-30yrs, based on a DATE of format 1970-01-01. Can this be done accurately within a MySQL query?

[aschk]

...
WHERE birth_date_column BETWEEN (DATE_SUB(NOW(), INTERVAL 30 YEAR)) AND (DATE_SUB(NOW(), INTERVAL 20 YEAR));

 

or

 

...
WHERE YEAR(FROM_DAYS(DATEDIFF(CURDATE(), birth_date_column))) BETWEEN 20 AND 30

 

note: i'm not 100% on the 2nd method as i thew it together in a few minutes.

[aschk]

Actually having done a test with both i'm not convinced either work as expected.

[Helmet]

Hey aschk, thanks for your reply. This is what I ended up doing, using PHP for the tough stuff, which seems to work, but looks nasty:

 

$today = date("Y-m-d");
list($year,$month,$day) = explode("-",$today);
$fromyear = $year - $form['agefrom'];
$fromdob = $fromyear."-".$month."-".$day;
$toyear = $year - $form['ageto'];
$todob = $toyear."-".$month."-".$day;

$sql = "SELECT ID FROM accounts WHERE birthdate BETWEEN '$todob' AND '$fromdob'"

 

 

[fenway]

It's possible to adapt mysql birthday code to handle a range... let me know if you care, I can dig it up

[Helmet]

Sure thing.. I'd love to see a way to do this a little cleaner..

[fenway]

this refman page has some simple examples on how to get the age... should be trivial to use BETWEEN based on this value.