All Forums UDA
newbie1234567 2 posts Joined 02/09
25 Feb 2009
Date of birth MMDDYYYY text convert to date format and get age

I've gotten halfway through this and am hoping I'm close to the endit should not take a day to get a single field updated, Anywaygot a Char(8)field formatted MMDDYYYY formatted to a date by using SELECT(CAST(SUBSTR(CHAR_DOB,1,4) || '/' || SUBSTR(CHAR_DOB,5,2) ||'/'||SUBSTR(CHAR_DOB,7,2) AS DATE)) AS DATE_DOBnow i need to do a current date minus the above to get ageIf anyone knows the command Im looking for, itd be greatly appreciated

newbie1234567 2 posts Joined 02/09
25 Feb 2009

Newbies may not be as smart as dinosaurs, but we sure are faster!Here's the SQL to get age from a char(8) date of birth fieldSELECT (CURRENT_DATE - (CAST(SUBSTR(CHAR_DOB,1,4) || '/' || SUBSTR(CHAR_DOB,5,2) ||'/'||SUBSTR(CHAR_DOB,7,2) AS DATE)))/365 AS AGEFROM DB_WRK_IAS.IAS_MSP_MSPT001

CarlosAL 512 posts Joined 04/08
26 Feb 2009

What about leap years? Your math is not completely correct.You may take a look here : http://carlosal.wordpress.com/2008/10/07/%c2%bfque-edad-tienes-en-teradata/ .HTH.Cheers.Carlos.

novice 38 posts Joined 07/07
26 Feb 2009

To account for the leap year, we could divide by 365.25 to get a more precise number of years.Calculate age as select (Current_Date - DOB )/365.25 from table;cheers...

CarlosAL 512 posts Joined 04/08
27 Feb 2009

...And this is still NOT accurate, since the duration of a solar year is slightly less than 365.25 days and thus there are exceptions (for example: years that are evenly divisible by 100 are not leap years, unless they are also evenly divisible by 400)Cheers.Carlos.

jjx5nc 7 posts Joined 01/12
04 Jan 2012

Also you don't have 1/4th of a leap day every year of a person's life.  People under age 4 may have no leap days.  Something to consider.

JJx5

You must sign in to leave a comment.