help getting age from date of birth in mysql field...

[spewn]

i'm using mysql.

here's the code i'm trying to get to work:

$sth = $dbh->prepare("SELECT DATEDIFF(yyyy,mDOB,CURRENT_DATE) from my_table");

$sth->execute;

use constant AGEX => 0;

$i=0;
while (@row = $sth->fetchrow_array()){
$age1 = $row[AGEX];
print $age1.'<br>';
$i++;
}

this just does not work. any ideas why?

i believe the syntax is just not right.

thanks!

- g

 

[Kirsle]

This sounds like a question for a SQL forum.

For Perl you could just select the date as-is and use Date::Calc (specifically the Delta_* functions).

Cuvou.com | My personal homepage

perl -e '$|=$i=1;print" oo\n<|>\n_|_";x:sleep$|;print"\b",$i++%2?"/":"_";goto x;'

 

[TrojanWarBlade]

Firstly, datediff as I understand it takes only 2 parameters and you're trying to feed it three.
From your code it looks to me like you're trying to get the difference in years between the dates (i.e. age in years).
Maybe you should just select the DOB and do a manual calculation like this:

my $dob = '1937-11-15'; # This would come from the DB.
print age_in_years($dob), "\n";

sub age_in_years {
    my $dob = shift;

    # Break dob into parts
    $dob =~ m{(\d\d\d\d)-(\d\d)-(\d\d)}
        or die "age_in_years(): Invalid date parameter of [$dob] supplied";
    my ($year, $month, $day) = ($1, $2, $3);

    # Sanity check
    die "age_in_years(): Invalid date parameter of [$dob] supplied"
        unless($year > 1800 && $year  < 2100
            && $month > 0   && $month < 13
            && $day   > 0   && $day   < 32);

    # Next get "now" time components
    my ($nyear, $nmonth, $nday) = (localtime)[5,4,3];

    # Apply standard corrections to localtimes idea of a date
    $nyear += 1900; $nmonth++;

    # Start age calculation
    my $result = $nyear - $year;

    # Correct year count if not yet reached birthday in current year
    $result-- if($nmonth < $month || ($nmonth == $month && $nday < $day));

    return $result;
}

Trojan.

 

[ChrisHunt]

Seems a bit excessive to split it out into umpteen lines of perl when you can do it in a simple(ish) SQL expression. I don't have mySQL to hand, but this should work:

$sth = $dbh->prepare("SELECT (YEAR(CURRENT_DATE()) - YEAR (mDOB)) - (DAYOFYEAR(mDOB) > DAYOFYEAR(CURRENT_DATE()) from my_table");

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[stevexff]

SELECT (YEAR(CURRENT_DATE) - YEAR('1960-06-16')) AS AGE...

might do the trick.

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[ChrisHunt]

A straight year minus year calculation won't work, as you need to allow for people who haven't had their birthday yet this year.

If my 30th birthday will be on 1st August, and it's 31st July today, then I'm still 29 (I wish!). That's kinda important to some people.

The code I posted above comes close, but, on reflection, I think it will have problems with leap years. So here is the version recommended in the MySQL manual...

(YEAR(CURDATE())-YEAR(mDOB)) - (RIGHT(CURDATE(),5)<RIGHT(mDOB,5))

The purist in me hates to see date, boolean and string expressions all mashed in together, but that's MySQL for you!

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd