How come this query returns ages that are 19 and 20 and not 20?

[kevinkhan]

SELECT  ROUND( DATEDIFF( NOW( ) , STR_TO_DATE( bday, '%M %d,%Y' ) ) / 365 ) AS age FROM profile_list WHERE TRUE AND ROUND( DATEDIFF( NOW( ) , STR_TO_DATE( bday, '%M %d,%Y' ) ) / 365 ) = 20

 

 

i know this isnt the best format but the date of births are stored like this

 

April 10, 1991

May 6, 1991

 

How come the query returns ages that are 19 and 20 and not just 20 like what i want?

 

i only want the dates to be return which are between todays date which is the 1/20/2011 and 1/20/2012

[PFMaBiSmAd]

There aren't exactly 365 days in each year.

 

See this link for how you can calculate a person's age - http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html

[kevinkhan]

ok i think i need to change my bday comumn in my database into the correct format and use date datatype

 

is there a way of converting all the existing dates which are `bday` text CHARACTER SET latin1 NOT NULL, into a date datatype

and anywhere the existing format is like "February 4" and the year is not included have it insert like 0000-02-04 otherwise if the date is like August 22, 1985 have it formated like 1985-08-22

 

Thanks for your help

[fenway]

There's always a way -- just figure out how many different formats you have, and update accordingly.