how to handle invalid date in Oracle SQL

user_15320888 0
Viewed 1000

There is a column that displays some statements for transactions

Ex: 'date-05-03-2020-Tran-100002345-W.44321-CR-9999eu843'

And I fetched out only the date from the statement. In some cases, there is an invalid date such as date-05-032-2020 (three numbers in month instead of two numbers)

So when I executed the query I got an error as I am using TO_DATE function to convert the varchar to date after extracting it from the previous statement

So what I need is a function like IfError in excel sheet but in oracle SQL

Ex: if the DATE_x is invalid then replace it with DATE_x2

with x as(
select TO_DATE('01/20/2021','DD-MM-YYYY') DATE_x,
TO_DATE('01/02/2021','DD-MM-YYYY') DATE_x2
from dual
)

select 
DATE_x2
from x;

Please advise

date oracle exception sql
2 Answers

There is a default... on conversion error parameter in to_date: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/TO_DATE.html#GUID-D226FA7C-F7AD-41A0-BB1D-BD8EF9440118

So you can write TO_DATE('01/20/2021' default '01-01-2020' on conversion error, 'DD-MM-YYYY') or TO_DATE('01/20/2021' default null on conversion error ,'DD-MM-YYYY').

There is also validate_conversion function: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/VALIDATE_CONVERSION.html#GUID-DC485EEB-CB6D-42EF-97AA-4487884CB2CD

user_4291000

One option is to check date format with regexp_like. Example you posted suggests it is 'dd-mm-yyyy' which means [2 digits - 2 digits - 4 digits]. Certainly, [43-85-0123] "matches" such a format but doesn't represent a valid date. Anyway, see if it helps. In my example, I'm substituting an invalid date value with today's date. Read comments within code.

SQL> with test (col) as
  2    -- sample data; the 2nd row is invalid
  3    (select 'date-05-03-2020-Tran-100002345-W.44321-CR-9999eu843'  from dual union all
  4     select 'date-05-032-2020-Tran-100002345-W.44321-CR-9999eu843' from dual
  5    ),
  6  exdate as
  7    -- extract "date" substrings (values between 1st and 4th "-" character)
  8    (select col,
  9            substr(col, instr(col, '-') + 1,
 10                        instr(col, '-', 1, 4) - instr(col, '-') - 1
 11                  ) datum
 12     from test
 13    )
 14  select col,
 15         case when regexp_like(datum, '[0-9]{2}-[0-9]{2}-[0-9]{4}') then
 16                   to_date(datum, 'dd-mm-yyyy')
 17              else trunc(sysdate)
 18         end as result
 19  from exdate
 20  /

COL                                                  RESULT
---------------------------------------------------- ----------
date-05-03-2020-Tran-100002345-W.44321-CR-9999eu843  05-03-2020
date-05-032-2020-Tran-100002345-W.44321-CR-9999eu843 01-08-2021

SQL>
user_90979060
Related