Calculating Date of Birth

I have a table with Register_ Year, Date of Birth.  I need to calculate Age using 01-10-Register_ Year and Date of Birth.  Can anyone help with this in Oracle
Coco BeansDesignerAsked:
Who is Participating?
 
sarabandeCommented:
you better should try straight-forward

TO_NUMBER(REGISTER_YEAR) - YEAR(BIRTH_DATE)

Open in new window


and avoid those many unneeded conversions.

Sara
0
 
slightwv (䄆 Netminder) Commented:
I would use:  MONTHS_BETWEEN(to_date('01-20-'||register_year,'MM-DD-YYYY'),DOB)/12

It might not be exact but you get the idea.
0
 
Mark GeerlingsDatabase AdministratorCommented:
What are the data types of your two columns: Register_ Year and Date of Birth?

If "Date of Birth" is already a "date" column, it seems that you would not need to calculate a date of birth.  But, if that column is actually a VARCHAR2, CHAR or NUMBER column that contains a month and day, then doing this calculation becomes more complex.  The biggest challenge then is likely to be "dirty data", that is: values that don't fit a consistent pattern, or are incomplete, or have invalid values, like "Feb. 30" or "13" for a numeric month.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sarabandeCommented:
If "Date of Birth" is already a "date" column, it seems that you would not need to calculate a date of birth.

the title was wrong. the right question is  

 
How to calculate the age from 'register year' and 'date of birth'. 

Open in new window


as told in the body of the question.

my problem is that i don't know what
1-10 register_year
 is? is it a 4-digit year (string) as assumed by slightwv? or is it the indices 1,2,..10 pointing to a table of register years?

Coco, can you give better information?

Sara
0
 
Coco BeansDesignerAuthor Commented:
Calculated like this..


TRUNC((TO_NUMBER(REGISTER_YEAR||'0101')-TO_NUMBER(TO_CHAR( BIRTH_DATE ,'yyyymmdd')))/10000)
0
 
slightwv (䄆 Netminder) Commented:
>>you better should try straight-forward

That would be great if YEAR was an Oracle function:
to_number(to_char(birth_date,'yyyy'))
or
extract(year from birth_date)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.