getting age in years from database based on date of birth...

[spewn]

hello!

i know i have to use DateDiff, as in this format:

DATEDIFF(yyyy,mDOB,getdate())

but not sure how to format the sql query.

my field for date of birth is mDOB, and i want to return age for each entry in db.

any help?

- g

 

[johnwm]

I would have used DATE_SUB for this. See DATE_SUB for syntax and usage

If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[r937]

your use of Getdate() suggests that you are not actually using MySQL but perhaps Microsoft SQL Server???

here's the formula --

SELECT YEAR(CURRENT_DATE) 
       - YEAR(mDOB)
       - CASE WHEN MONTH(CURRENT_DATE)
                 > MONTH(mDOB)      
              THEN 0
              WHEN MONTH(CURRENT_DATE)
                 < MONTH(mDOB)      
              THEN 1
              WHEN DAY(CURRENT_DATE)
                 < DAY(mDOB) 
              THEN 1
              ELSE 0 END    AS age
  FROM ...

note that CURRENT_DATE is the standard SQL function for today's date -- MySQL supports it, but SQL Server doesn't



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[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.

- g

 

[r937]

i can't understand your perl code, sorry

did you try the age code i posted? it's actually more accurate than your DATEDIFF

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon