Calculating Date of Birth

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.

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.

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

Select allOpen 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

Calculated like this..


TRUNC((TO_NUMBER(REGISTER_YEAR||'0101')-TO_NUMBER(TO_CHAR( BIRTH_DATE ,'yyyymmdd')))/10000)

>>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)