I have a create_date field of type varchar2(). There are invalid values ​​like this:

How can I exclude them so that I can insert them from this table into another table in a field with a date value? I need correctly entered dates like 10/02/2022, 08/05/2021, etc.

CodePudding user response：

You may use validate_conversion function to filter rows that contain invalid string representation of a date:

with a(dt) as (
  select *
  from sys.odcivarchar2list(
  '21.10.2022',
  '31.02.2022',
  'Не дата',
  '01.12.2021',
  '00.10.2021',
  '   '
  )
)
select
  dt as char_value,
  to_date(dt, 'dd.mm.yyyy') as date_value
from a
where validate_conversion(dt as date, 'dd.mm.yyyy') = 1

fiddle