Oracle query to calculate current age

I want to calculate current age of person from DOB(date of birth) field in Oracle table.

Data type of DOB field is varchar and the is date stored in format ‘DD-MON-YY’.

when I calculate current age of a person from date like 10-JAN-49 the query will return age in negative. Also, I observed that if date has year 13 to 49 it gives negative result.

Examples

22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36

Query Executed for reference

select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs' 
from birth

Any help is appreciated!

Answer

To get round the 21st century problem, just modifying @the_silk’s answer slightly:

SELECT
  CASE WHEN SUBSTR(dob, -2, 2) > 13
  THEN FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
  ELSE
       FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
  END
FROM
birth

Please be aware though that this assumes that any date year between ’00’ and ’13’ is 21st century, so this sql should only be used if you are building a one off throwaway script, otherwise it will become out of date and invalid before long.

The best solution would be to rebuild this table, converting the varchar column into a date column, as alluded to by Ben.