Correct way to get age?

I'm trying to get the age of a player at the date of each match.

Currently I have this:

``````SELECT PlayerName, MatchID, DATEDIFF( MatchDate, DOB )
FROM MatchPlayerResult
INNER JOIN Player ON MatchPlayerResult.PlayerID = Player.PlayerID
``````

What is the best way to return the DATEDIFF( MatchDate, DOB )  as the actual age?
Who is Participating?

ArtistCommented:
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(MatchDate, DOB)+1), "%Y")+0 AS age from MatchPlayerResult
0

ArtistCommented:
function datediff(\$matchtime,\$dob) {

\$lastYear = substr(\$matchtime, 0, 4);
\$lastMonth = substr(\$matchtime, 5, 2);
\$lastDay = substr(\$matchtime, 8, 2);
\$T1 = mktime(0,0,0, \$lastMonth, \$lastDay, \$lastYear);

\$lastYear = substr(\$dob, 0, 4);
\$lastMonth = substr(\$dob, 5, 2);
\$lastDay = substr(\$dob, 8, 2);
\$T2 = mktime(0,0,0, \$lastMonth, \$lastDay, \$lastYear);

\$diff = floor(\$T2-\$T1);
return \$diff;
}
0

Author Commented:
Thanks, I tried to correct it though and get this error:

``````create function GetDiffMatchDateDOB(\$matchtime,\$dob) {

\$lastYear = substr(\$matchtime, 0, 4);
\$lastMonth = substr(\$matchtime, 5, 2);
\$lastDay = substr(\$matchtime, 8, 2);
\$T1 = mktime(0,0,0, \$lastMonth, \$lastDay, \$lastYear);

\$lastYear = substr(\$dob, 0, 4);
\$lastMonth = substr(\$dob, 5, 2);
\$lastDay = substr(\$dob, 8, 2);
\$T2 = mktime(0,0,0, \$lastMonth, \$lastDay, \$lastYear);

\$diff = floor(\$T2-\$T1);
return \$diff;
}
``````

gives:

Error
SQL query:

CREATE FUNCTION GetDiffMatchDateDOB(

\$matchtime,
\$dob
){\$lastYear = SUBSTR( \$matchtime, 0, 4 ) ;

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\$dob) {

\$lastYear = substr(\$matchtime, 0, 4)' at line 1
0

ArtistCommented:
Remember, the function is returning a value, not the variables.  \$dob is used only in the function.

\$difference = GetDiffMatchDateDOB(\$matchdatefromdatabase, \$dobfromdatabase);

If you are getting the database values with a query within the function, you won't need the arguments to the function.
0

Author Commented:
Sorry I don't understand your point.

When I try and run the above script, I get a query error and it isn't able to create the function so I can't even call it at this stage because I can't create it.
0

ArtistCommented:
What I gave you is NOT a MySQL function, it is a PHP function.  If you are trying to write a MySQL stored procedure to do this, see here:

http://dev.mysql.com/doc/refman/5.0/en/stored-routines.html

DATEDIFF() gives you the date difference in days, so if you don't need to be toooo precise, you could just divide that by 365 and take FLOOR() of the result.

SELECT  FLOOR(DATEDIFF( MatchDate, DOB )/365) as Age
0

Author Commented:
Thanks. Sorry, I thought that was mysql related because the thread is in the mysql queue.

Is there a way to get more precise using just a mysql result, so I can include it within the query criteria?
0

ArtistCommented:
Try this on your data and see if it gives you the result you want:

SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(MatchDate, DOB)), "%Y")+0 AS age from MatchPlayerResult
0

Author Commented:
It doesn't work properly unfortunately.

See the attached example data. For the highlighted yellow player, the Match Date and DOB, fall on the same mm/dd but instead of showing the player as 33, it shows the player as 32.

How can we get the correct data in that instance?
DOB.JPG
0

Author Commented:
Thank you :)
0

ArtistCommented:
Sorry for the initial confusion, thanks for the points, glad I could help.
0

Author Commented:
No problem, no need to be sorry. Appreciate us getting there :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.