Oracle SQL: TRY_CONVERT equivalent/ work around

user_13152439 0
Viewed 228

I'm dealing with a particularly bad dataset with dates stored as part of free text.

I'm using to_date(regexp_substr(dateformat)) to find all of the dates I can. I don't expect to grab all of them but as many as possible is good!

However, it seems sometimes that my regular expression picks up dates outside of my recognised date format, and I get the error: "date format picture ends before converting entire input string"

I would like to use something like try_convert in SQL Server to work around this, and just skip dates that come out as a problem.

Any ideas on how best to go about this?

PS Using Oracle SQl Developer 3.0.1 (No opportunity to update this unfortunately...)

oracle sql
1 Answers

You can use function like this:

CREATE OR REPLACE FUNCTION to_date_safe(is_val IN VARCHAR2, is_fm IN VARCHAR2)
    RETURN DATE 
IS
    ld_result DATE;
BEGIN
    RETURN to_date(is_val, is_fm);
EXCEPTION
    WHEN OTHERS
    THEN
        RETURN NULL;
END;
/
user_112720440
Related