3 Replies Latest reply on Aug 20, 2012 7:20 AM by Peter Hopwood

    Convert string to date, where some data will be invalid

    Willem Botha

      I'm trying to convert an ID number to a date of birth ex. the ID number 830209 5127 082 translates to 9 Feb 1983, the first six digits indicating YYMMDD.

       

      I'm doing fine when all the numbers are valid, but no validation happens before they are entered into my DB, so some don't translate to a valid date (ID number entered as XXXXXXX for instance) and I get the following error:

       

      Oracle database error 1843: ORA-01843: not a valid month

       

      I use the date of birth to calculate the age range of customers on a given date, flagging them as either YOUTH or ADULT, so any ideas on returning "UNKNOWN" in instances where this date returns an invalid value?