CODEWITHSUNDEEP
sqloracle-databasedateora-01858
M4AGIC
M4AGIC

Reputation: 1

Return null if date is not in date format

I have couple of thousand rows query that was working fine for a long time and at some point started returning:

ORA-01858: a non-numeric character was found where a numeric was expected
01858. 00000 -  "a non-numeric character was found where a numeric was expected"
*Cause:    The input data to be converted using a date format model was
           incorrect.  The input data did not contain a number where a number was
           required by the format model.
*Action:   Fix the input data or the date format model to make sure the
           elements match in number and type.  Then retry the operation.

I'm 99% sure it is date inserted with error.

My question here would be is there a function that would return a date or NULL if date is not in correct format?

Upvotes: 0

Views: 927

Answers (1)

Andrew Sayer
Andrew Sayer

Reputation: 2336

To_date Can do exactly that for you since version 12.2. See the docs https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/TO_DATE.html

select to_date('not a date' default null on conversion error,'yyyymmdd') 
from dual;

Upvotes: 5

Related Questions