2. PHPRunner General questions
  3. Age in years as of a certain date
This topic is locked

Age in years as of a certain date

 		4/29/2010 11:55:04 PM
PHPRunner General questions
V
 vickiwatson author

What is the easiest way to store the age (years only needed) as of a certain date? I have a birthdate field. The client wants it to automatically calculate a child's age as of Sept. 1 of whatever the current year is, so the value would change each year obviously.
Can this be done in MYSQL or as a PHP function? It would need to be displayed/updated on the add/edit/list pages.
I don't use either enough to figure out how to approach this.
A
 		ann 4/30/2010

Vicki,
It can be done by MySQL function Year(). Pick your table on the Edit SQL query tab and edit SQL query in the following way:

select field1,

...

year(now())-year(dateof)

from TableName



where dateof is your actual name.

More info here:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_year
Please note: you can't edit calculated field. So uncheck add/edit pages for this field on the "Choose fields" tab.
V
 		vickiwatson author 4/30/2010



year(now())-year(dateof)


Will that work with the Sept. 1 stipulation? If now() returns May 1 and their birthday is June 1, it would show them a year younger than I need, I think. Because their birthday is before Sept. 1, but hasn't occurred yet.
I need to do something like year(Sept. 1 of whatever the current year is)-year(dateof) but I don't know the correct syntax for that.
A
 		ann 5/3/2010

Vicki,
try this code:

SELECT

id_user,

dateof,

year(now())-year(dateof)-((09>month(dateof))) AS `year`

FROM users_info