Page 1 of 1
Calculating age using date
Posted: Tue Jan 17, 2006 2:29 am
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.
Posted: Tue Jan 17, 2006 2:48 am
by kcshankar
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
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)
Posted: Tue Jan 17, 2006 4:33 am
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
Thanks
Posted: Tue Jan 17, 2006 4:55 am
by LANDO
Thanks all for replying.
I eventualy decided to embed this calculation in the way I derivr the column from the DB.
Thanks.
Re: Calculating age using date
Posted: Wed Jan 18, 2006 2:58 am
by sekr
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
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,
Posted: Mon Oct 08, 2018 3:38 pm
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.
Posted: Mon Oct 08, 2018 4:07 pm
by ray.wurlod
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
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...