To calculate age from DOB and inner join of two tables and then draw Graph

Question

I have two tables TABLE1 and TABLE2 and through inner join of these two tables I will get the DOB( DD/MM/YYYY format). Now I have to calculate the age using this DOB and then draw a graph based based on this age(using java). I have no idea how to do it.

NOTE: I tried to look for this but I couldn't find a suitable solution for it. UPDATE: I know that I can use PrimeFaces easily to draw graph but I for that I need age.I am having problem in getting age.

UPDATE Code to get DOB

Select TABLE1.dob 
from TABLE1 INNER JOIN TBALE2 on TABLE1.id = TABLE2.id

@RafaEl Like I said, I have no idea how to do it.I am really new to SQL and hence have no idea abt it. I can get the DOB from inner join but then how to convert it into age is what I dont know — user2916886, Apr 08 '14 at 05:29
_"I can get the DOB from inner join.."_ I see you got something there. How you do it? maybe we can modify from it — Baby, Apr 08 '14 at 05:31
@RafaEl I have updated above with the SQL statement for getting DOB — user2916886, Apr 08 '14 at 05:35
you may check [this question](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — Baby, Apr 08 '14 at 05:52
@Rafa El even if the answer there is upvoted 54 times, it's a bad one. The answer isn't correct. Leap days will break such attempts. It's easy to avoid such traps see my answer below. — VMai, Apr 08 '14 at 06:07
@Rafa El: the third best rated answer seems to me the first good one. Most answers there are giving bad advice. They would fail on simple tests with leap days. — VMai, Apr 08 '14 at 06:28

VMai · Answer 1 · 2014-04-08T05:51:48.297

Getting the current age from date of birth is a question wether the person celebrated his birthday this year or not, pseudocode:

if person already celebrated the birthday
    age = current year - year of birth
else
    age = current year - year of birth - 1

Using date and time functions of mysql this is not too difficult, see first comment of http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#comments

Number of years between date1 and date2:

IF((YEAR(date2) - YEAR(date1)) > 0, (YEAR(date2) - YEAR(date1)) - (MID(date2, 6, 5) < MID(date1, 6, 5)), IF((YEAR(date2) - YEAR(date1)) < 0, (YEAR(date2) - YEAR(date1)) + (MID(date1, 6, 5) < MID(date2, 6, 5)), (YEAR(date2) - YEAR(date1))))

You could simplify this, because you know which date is the newer one.

To calculate age from DOB and inner join of two tables and then draw Graph

1 Answers1