Over the weekend I was speaking with one of the swim team parents who was asking me about our web site and I showed her what she could see about her daughter. As soon as I popped her daugher’s information she noticed that she was placed in the wrong age group. Oops.
I had seen this situation previously but couldn’t pin it down but now that I had a swimmer I could test against, I dug into it. It turns out that I had made the age calculation much, much, harder than it really needed to be. And it was wrong.
I was trying to use MySQL’s PERIOD_DIFF() function so the age and cutoff date adjusted age would be returned as part of the query. It turns out that PERIOD_DIFF() only accounts for months (which I knew when I used it) but not taking into account the days causes the calculation to be wrong and limits the age cutoff date to be the first of a month which may or may not be acceptable.
My first search yielded this example which turns out to be wrong as it doesn’t always return the correct age.
Here is the correct SQL to calculate age:
SELECT YEAR(birthdate) - (MONTH(CURRENT_DATE()) < MONTH(birthdate)) - ((MONTH(CURRENT_DATE()) = MONTH(birthdate)) & (DAY(CURRENT_DATE()) < DAY(birthdate))) AS age
Here is the correct SQL to calcualte age based on a specific cutoff date, in this case the cutoff date is June 1st:
SELECT YEAR(CURRENT_DATE()) - YEAR(birthdate) - (MONTH('2008-06-01') < MONTH(birthdate)) - ((MONTH('2008-06-01') = MONTH(birthdate)) & (DAY('2008-06-01') <= DAY(birthdate))) AS agegroupage
By using the calculations, the swimmer’s age and age group age are always returned with the query as opposed to computed on the fly.