Calculating age using date
Moderators: chulett, rschirm, roy
Calculating age using date
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.
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
Lando
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Assuming you have a table in Oracle called person as shown below
You could extract the person's name, date of birth and age (in years) using the following query
Code: Select all
SQL> desc person
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(30)
DOB DATE
SQL> select * from person;
NAME DOB
------------------------------ -----------
Tom 12-Sep-1980
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
Re: Calculating age using date
covert system date and date into into icnov and then minus ..
after that divided 365..
i think this will help u!
after that divided 365..
i think this will help u!
In Adv Thanks
---------s
---------s
-
- Participant
- Posts: 74
- Joined: Thu Jan 05, 2006 2:07 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: