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

Open in new window


What is the best way to return the DATEDIFF( MatchDate, DOB )  as the actual age?
PeterErhardAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Cornelia YoderArtistCommented:
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
PeterErhardAuthor 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;
} 

Open in new window


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
Cornelia YoderArtistCommented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

PeterErhardAuthor 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
Cornelia YoderArtistCommented:
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
PeterErhardAuthor 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
Cornelia YoderArtistCommented:
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
PeterErhardAuthor 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
Cornelia YoderArtistCommented:
SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF(MatchDate, DOB)+1), "%Y")+0 AS age from MatchPlayerResult
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PeterErhardAuthor Commented:
Thank you :)
0
Cornelia YoderArtistCommented:
Sorry for the initial confusion, thanks for the points, glad I could help.
0
PeterErhardAuthor Commented:
No problem, no need to be sorry. Appreciate us getting there :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.