Avg of date

[tobeyt23]

I am trying to get the average age based off the date of birth (mm/dd/yyyy) this is what I have but keep getting 51. Sure this is correct any suggestions?

 

SELECT FLOOR(AVG(TO_DAYS(NOW())-TO_DAYS(CONCAT(SUBSTR(user_profiles.dob, 7,4),'-',SUBSTR(user_profiles.dob, 1, 2),'-',SUBSTR(user_profiles.dob, 4, 2))))/365.242199) FROM user_profiles WHERE user_profiles.dob !=''

[fenway]

I have no idea what that means.

[tobeyt23]

I would like to get the avg age based off the dob each user enters which I have stored in the table as mm/dd/yy. I have the above query in place however it returns either 51 or 50.

[fenway]

Either 51 or 50? What does that mean?

[tobeyt23]

It returns either avg age of 50 0r 51 .... this now is stuck on 60 buy have added a ton of new users and doesn't seem to be working correctly. Any help?

[tobeyt23]

What i have is the dob field in this format MM/DD/YYYY and need to get that avg age based on that for all users.

[tobeyt23]

Think this does it:

 

SELECT ROUND((YEAR(NOW()) - AVG(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')))) FROM user_profiles WHERE user_profiles.dob != '' AND YEAR(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')) != YEAR(NOW()) AND YEAR(STR_TO_DATE(user_profiles.dob,'%m/%d/%Y')) != (YEAR(NOW())-1)