Oracle SQL: TRY_CONVERT equivalent/ work around

Question

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

you can use the custom function in this answer like try_convert https://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows — psaraj12, Mar 30 '20 at 11:46
Does this answer your question? [How to handle to\_date exceptions in a SELECT statment to ignore those rows?](https://stackoverflow.com/questions/5966274/how-to-handle-to-date-exceptions-in-a-select-statment-to-ignore-those-rows) — mustaccio, Mar 30 '20 at 13:28
As an aside, the version of SQL Dev you are using is totally irrelevant. It's the _database_ that processes the SQL, not SQL Dev. Nor any other client program. — EdStevens, Mar 30 '20 at 13:45

score 1 · Answer 1 · answered Mar 30 '20 at 12:51

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;
/

Oracle SQL: TRY_CONVERT equivalent/ work around

1 Answers1