DSXchange

Posted: **Tue Jan 17, 2006 2:29 am**

Hello,

How can I calculate a person age using dates in a transformation
in SQL it would look something like that :

SELECT round((SYSDATE - to_date('01/01/2005','dd/mm/yyyy'))/365)
FROM dual

I have the birthdate of the person and in order to calculate the age I'm substructing the birthdate from SYSDATE (I'm using DSJobStartTimestamp function, but it looks like a string converted to date so it doesn'y work)

Any suggestions ?

P.S.: I searched the forums but didn't find anything usefull - yet.

Posted: **Tue Jan 17, 2006 2:48 am**

Hi Lando,
Try Built in Transform function,DateCompareGenericDateDiff.
It Compares two dates and returns the number of days between them.

regards
kcs

Posted: **Tue Jan 17, 2006 2:59 am**

You can convert the date of birth into internal format using Iconv() then subtract that value from system variable @DATE, which is already in internal format, to get age in days. Divide by the average number of days in a year (365.249) and apply the Int() function to yield complete years.

Code: Select all

Int((Iconv(InLink.DOB, "DMDY") - @DATE) / 365.249)

Posted: **Tue Jan 17, 2006 4:33 am**

Assuming you have a table in Oracle called person as shown below

Code: Select all

SQL> desc person
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 NAME                                               VARCHAR2(30)
 DOB                                                DATE


SQL> select * from person;

NAME                           DOB
------------------------------ -----------
Tom                            12-Sep-1980

You could extract the person's name, date of birth and age (in years) using the following query

Code: Select all

SQL> select name,dob,floor(((sysdate - dob)/365.25)) as age from person;

NAME                           DOB                AGE
------------------------------ ----------- ----------
Tom                            12-Sep-1980         25

Posted: **Tue Jan 17, 2006 4:55 am**

Thanks all for replying.

I eventualy decided to embed this calculation in the way I derivr the column from the DB.

Thanks.

Posted: **Wed Jan 18, 2006 2:58 am**

covert system date and date into into icnov and then minus ..
after that divided 365..
i think this will help u!

Posted: **Wed Jan 18, 2006 8:04 am**

Hi,

If your date is without timestamp you can use in tranformer -
Int((Date()-Iconv(Dob,"D-YMD[4,2,2]"))/365.25)

& if with timestamp then
Int((Date()-Iconv(Dob[1,10],"D-YMD[4,2,2]"))/365.25)

Thanks,

Posted: **Mon Oct 08, 2018 3:38 pm**

I thought this might round up, like it does without the "Int".
But when I tried it, it works well, it has rounded my age down to 42 even though I am nearly 43.
Thank you for your help.

Posted: **Mon Oct 08, 2018 4:07 pm**

i_m_b wrote:even though I am nearly 43.

Yes, but your actual age is 42.

Posted: **Tue Oct 09, 2018 9:49 am**

do you need some sort of logic to round up? That is doable but I have never heard of anyone doing that for your age in years. you can add a +1 if the # of days until next birthday is within N days, not hard to do...

DSXchange

Calculating age using date

Calculating age using date

Thanks

Re: Calculating age using date