I have a saved search that looks at a custom text field on a Sales Order. The custom field should contain an ISO date. I want to get all records where that date is greater than today. My first attempt was to try Formula(Date) with the formula below and condition "is greater than today"
TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD') However this gave me an "Unexpected Error" when I tried to do the search. I worked out this was because not every record has an ISO date, some are null and some have been edited to contain some non-date data. It seems that if any row returns an error the whole saved search fails. I therefore want to exclude anything that doesn't parse as a date from my search. However, I could find no way of catching an exception from the TO_DATE function. For the moment I have:
CASE WHEN REGEXP_LIKE({custbody_est_delivery},'(\d{4})-(\d{2})-(\d{2})') THEN TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD')ELSE TO_DATE('2099-12-31', 'YYYY-MM-DD') END This works, but it is a horrible hack and would fail if someone e.g. wrote in '9999-99-99' in the field.
How can I catch exceptions in a Netsuite saved search formula? I'm looking for something like the pseudocode below:
IFERROR(TO_DATE({custbody_est_delivery}, 'YYYY-MM-DD'), <do something sensible>) 
