DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
LANDO
Participant



Joined: 09 Jan 2006
Posts: 16

Points: 155

Post Posted: Tue Jan 17, 2006 2:29 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Windows
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


since February 2006

Group memberships:
Premium Members

Joined: 10 Jan 2005
Posts: 91

Points: 777

Post Posted: Tue Jan 17, 2006 2:48 am Reply with quote    Back to top    

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



regards
kcs

_________________
“Motivation is what gets you started. Habit is what keeps you going.”Jim Rohn
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Tue Jan 17, 2006 2:59 am Reply with quote    Back to top    

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

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
jzparad


since February 2006

Group memberships:
Premium Members

Joined: 01 Apr 2004
Posts: 151

Points: 1049

Post Posted: Tue Jan 17, 2006 4:33 am Reply with quote    Back to top    

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

Code:

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:

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

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

_________________
Jim Paradies
Rate this response:  
Not yet rated
LANDO
Participant



Joined: 09 Jan 2006
Posts: 16

Points: 155

Post Posted: Tue Jan 17, 2006 4:55 am Reply with quote    Back to top    

Thanks all for replying. Smile

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

Thanks.
Smile

_________________
Best Regards,
Lando
Rate this response:  
Not yet rated
sekr
Participant



Joined: 13 Dec 2005
Posts: 14

Points: 117

Post Posted: Wed Jan 18, 2006 2:58 am Reply with quote    Back to top    

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
Rate this response:  
Not yet rated
sourabhverma
Participant



Joined: 05 Jan 2006
Posts: 74

Points: 755

Post Posted: Wed Jan 18, 2006 8:04 am Reply with quote    Back to top    

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,
Rate this response:  
Not yet rated
i_m_b



Group memberships:
Premium Members

Joined: 29 May 2018
Posts: 1

Points: 5

Post Posted: Mon Oct 08, 2018 3:38 pm Reply with quote    Back to top    

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.
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 54407
Location: Sydney, Australia
Points: 295092

Post Posted: Mon Oct 08, 2018 4:07 pm Reply with quote    Back to top    

i_m_b wrote:
even though I am nearly 43.

Yes, but your actual age is 42.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong | Hobart | Brisbane
currently hiring: Canberra, Sydney and Melbourne (especially seeking good business analysts)
Rate this response:  
Not yet rated
UCDI



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 336

Points: 3396

Post Posted: Tue Oct 09, 2018 9:49 am Reply with quote    Back to top    

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...
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours