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
22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36
select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs'
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.