Age Calculations

Question

I am trying to calculate ages for people with the respect to monthly data so their birth date will reflect a round number in the given month.
However I have several rows that provide me with negative ages.

Is there any way in this formula to make it
1. Absolute Value ?
2. Birth date is greater than the year just give me 0?

floor(months_between(to_date('31-JAN-14','DD-MON-YY'),DATE_OF_BIRTH )/12) as AGE

Looks like you've found one of the problems with two digit years. Those future birth dates probably occurred in the 1900's or earlier, but by passing just the last two digits you've lost the century info. If you are the one loading the age data, try changing the format mask from 'DD-MON-YY' to 'DD-MON-RR' to make a better guess at the correct century instead of just using the current century. — Sentinel, Aug 18 '15 at 14:16
What does it mean for the `Birth date` to be greater than the year? Which year? Are we just comparing years or do months and days matter? In the above example if DOB were 1-Jun-14 then years are the same (they are both 14), but the DOB is in the future so your function returns an age of -1 the ABS of which is 1. Is that your desired result? — Sentinel, Aug 18 '15 at 14:43

score 1 · Accepted Answer · answered Aug 18 '15 at 13:34

1

With regards to your first question, perhaps you're after this:

floor(abs(months_between(to_date('31-JAN-14','DD-MON-YY'),DATE_OF_BIRTH )/12)) as AGE

I'm not sure if the above also answers your second question, or whether you're after a case statement such as:

case when date_of_birth > p_some_year then 0
     else floor(abs(months_between(to_date('31-JAN-14','DD-MON-YY'),DATE_OF_BIRTH )/12))
end as age

answered Aug 18 '15 at 13:34

Boneist

22,910
1
25
40

and what's the error? (first comment I have is that `to_date('E1.Year_Month', 'YYYY-MM')` should be `to_date(E1.Year_Month, 'YYYY-MM')`. Ie. by putting the single quotes around E1.Year_Month, you're telling Oracle that it's a string, which is probably not what you want to do...) – Boneist Sep 02 '15 at 14:32
Sorry. I fixed it. Thank you for your continued help. – Tinkinc Sep 02 '15 at 14:54

score 0 · Answer 2 · answered Aug 18 '15 at 14:29

Rather than taking the absolute value or zero, you could just add 100 to the negative dates:

floor(months_between(to_date('31-JAN-14','DD-MON-YY'),DATE_OF_BIRTH )/12)
+ case when DATE_OF_BIRTH > to_date('31-JAN-14','DD-MON-YY') then 100
       else 0
  end as AGE

If you really do want an age of zero for future birth dates instead of a negative number or the solution above then use this:

greatest(0,floor(months_between(to_date('31-JAN-14','DD-MON-YY'),DATE_OF_BIRTH )/12)) as AGE

Absolute value is not needed for either of the above solutions nor for any other solution which returns zero for future dates of birth.

Age Calculations

2 Answers2