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
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