Calculating age using date

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
LANDO
Participant
Posts: 16
Joined: Mon Jan 09, 2006 3:35 am

Calculating age using date

Post by LANDO »

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.
Best Regards,
Lando
kcshankar
Charter Member
Charter Member
Posts: 91
Joined: Mon Jan 10, 2005 2:06 am

Post by kcshankar »

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



regards
kcs
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

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)
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
jzparad
Charter Member
Charter Member
Posts: 151
Joined: Thu Apr 01, 2004 9:37 pm

Post by jzparad »

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
Jim Paradies
LANDO
Participant
Posts: 16
Joined: Mon Jan 09, 2006 3:35 am

Thanks

Post by LANDO »

Thanks all for replying. :)

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

Thanks.
:)
Best Regards,
Lando
sekr
Participant
Posts: 14
Joined: Tue Dec 13, 2005 3:50 am

Re: Calculating age using date

Post by sekr »

covert system date and date into into icnov and then minus ..
after that divided 365..
i think this will help u!
In Adv Thanks
---------s
sourabhverma
Participant
Posts: 74
Joined: Thu Jan 05, 2006 2:07 am

Post by sourabhverma »

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,
i_m_b
Premium Member
Premium Member
Posts: 1
Joined: Tue May 29, 2018 11:10 pm

Post by i_m_b »

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.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

i_m_b wrote:even though I am nearly 43.
Yes, but your actual age is 42.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
UCDI
Premium Member
Premium Member
Posts: 383
Joined: Mon Mar 21, 2016 2:00 pm

Post by UCDI »

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...
Post Reply