Date of birth MMDDYYYY text convert to date format and get age

UDA

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 end
it should not take a day to get a single field updated,
Anyway

got 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_DOB

now i need to do a current date minus the above to get age

If anyone knows the command Im looking for, itd be greatly appreciated
5 REPLIES

Re: Date of birth MMDDYYYY text convert to date format and get age

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 field

SELECT (CURRENT_DATE -
(CAST(SUBSTR(CHAR_DOB,1,4) || '/' || SUBSTR(CHAR_DOB,5,2) ||'/'||SUBSTR(CHAR_DOB,7,2) AS DATE)))/365 AS AGE
FROM DB_WRK_IAS.IAS_MSP_MSPT001
Junior Supporter

Re: Date of birth MMDDYYYY text convert to date format and get age



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

Re: Date of birth MMDDYYYY text convert to date format and get age

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...
Junior Supporter

Re: Date of birth MMDDYYYY text convert to date format and get age



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

Re: Date of birth MMDDYYYY text convert to date format and get age

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.