Calculating age in years

dataservices job design
1

Is there anyway to calculate age in years in BODI??

Its basically table.column date - sysdate

Thanks

shalini (BOB member since 2008-03-26)

2

date_diff(start_date,sysdate(),‘YY’)

@rocky (BOB member since 2010-09-26)

3

Nope. This function won’t work for YY Parameter.
It can accept only D, H, M, S, i.e., Days, Hours, Minutes and Seconds respectively

ganeshxp :us: (BOB member since 2008-07-17)

4

I did it this way

ifthenelse(to_decimal(to_char(sysdate(),‘MMDD’),’.’,’’,0) >= to_decimal(to_char(start_date,‘MMDD’),’.’,’’,0),date_diff(sysdate(),start_date,‘MM’)/12, (date_diff(sysdate(),start_date,‘MM’)-1)/12)

The only thing I’m having to struggle with is rounding off the age.
If the number after the decimal is >=5, I want to show it as 5 (ex: 49.61 as 49.5) and if <5 then 0 (Ex: 49.48 as 49.0)

Any suggestions??

shalini (BOB member since 2008-03-26)

5

The ‘YY’ does work in the date_diff function At least in 12,2 it does. Tested it with this code:

$InputDate = to_date(‘1999.04.01’, ‘YYYY.MM.DD’);
$YearDiff = date_diff( $InputDate, sysdate(), ‘YY’);
Print('Difference in years = '|| $YearDiff );

Couldn’t you take the result from your code below and divide by days ina year. For example:

(table.column date - sysdate())/365.25

If you can get the days, just divide by the number of days in a year.

djordan :ireland: (BOB member since 2006-03-01)

6

Oh my bad. I am still not growing :oops:
Still in 11.7

ganeshxp :us: (BOB member since 2008-07-17)

7

Yes, it was implemented in 12.2 or one of the later releases at least.

Werner Daehn :de: (BOB member since 2004-12-17)

8

It depends if you’re looking for a number of whole years between two dates or a person’s age.

Here is the guts of a script function I wrote for the latter - usual disclaimers apply:

$Age = year($input_date) - year($date_of_birth);

if (to_char($input_date, ‘MMDD’) < to_char($date_of_birth, ‘MMDD’))
$Age = $Age - 1;

people born on 29 feb celebrate their birthday on the last day of feb.

- the logic above would put their birthday on 1 march in non-leap years

if (to_char($date_of_birth, ‘MMDD’) = ‘0229’ # born 29th Feb
and to_char($input_date, ‘MMDD’) = ‘0228’ # input date is 28th Feb
and to_char($input_date + 1, ‘MMDD’) != ‘0229’) # not a leap year
$Age = $Age + 1;

Return $Age;

dastocks (BOB member since 2006-12-11)

9

In any case, to be more precise, you could calculate date_diff using Days, then divide that by 365.25 (to account for leap years).

dnewton :us: (BOB member since 2004-01-30)

10

Old thread, but the above solution is what I had to use. I can’t tell if this is a bug or if this is just how Data Services does things.

date_diff using ‘YY’ is reporting an incorrect age.

print('19550423 ’ || date_diff(to_date(‘19550423’,‘YYYYMMDD’), sysdate(), ‘YY’));
print('19550424 ’ || date_diff(to_date(‘19550424’,‘YYYYMMDD’), sysdate(), ‘YY’));
print('19550425 ’ || date_diff(to_date(‘19550425’,‘YYYYMMDD’), sysdate(), ‘YY’));

All three results above are “62”. The value for 0425 should be 61.

The final calculation I used is this:
cast(floor(date_diff(Query_Date_Seq.BIRTHDATE, cast(sysdate(), ‘date’), ‘D’) / 365.25), ‘int’)

eganjp :us: (BOB member since 2007-09-12)