If the issue is just with null values, you could try using NVL or COALESCE to provide a default date when the field is null:

TO_DATE(SUBSTR(NVL({field}, '2020-01-01'), 1, 10), 'YYYY-MM-DD')

Yeah, I've tried with nvl() and it didn't help either. There are no records in the table that has null values.

Interesting. Do any of them have dates in a different format, or invalid characters, like the letter O instead of a zero, or non-ASCII look-alike characters (like a dash instead of a hyphen), that would break parsing? Curious why it would fail, since that formula looks completely correct. You could try this perhaps, removing all non-numeric characters:

TO_DATE(SUBSTR(REGEXP_REPLACE(field}, '[^[:digit:]]+', ''), 1, 8), 'YYYYMMDD')

No, have a look at the main thread https://netsuiteprofessionals.slack.com/archives/C2A1ZEMF0/p1607584978087200?thread_ts=1607503160.085100&cid=C2A1ZEMF0 I even created a testing record where I only had a single entry. It seems as it only works when the yyyy is not in the beginning of the text.

That is strange. The documentation here, https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm, shows this example:

TO_DATE('2008 05 20','YYYY MM DD')

nice to have someone else confirm it! 🙂 I have already created a case for it, but they are still in the initial stage of investigation.

according to the oracle docs in the link above, the separators don’t matter for normal formats, but if you put

fx

at the start of a format then it expects an exact match. so that seems consistent, at least. that was something i didn’t learn until just today by reading it.

Ok, didn't know that either.