convert varchar to date oracle plsql

Question

I need to convert a varchar for date so that you can filter all dates longer than a certain date, for example:

I want all dates that are larger than November 2018 to be listed, I am doing as follows:

DATA_FINAL > TO_DATE('10/11/2018', 'MM/DD/YYYY')

But I am always returned the error:

ORA-01843: not a Valid Month

What should I do? I thank you in advance.

Result of SUBSTR:

    1   09
    2   08
    3   04
    4   26
    5   28
    6   07
    7   17
    8   13
    9   11
    10  12
    11  01
    12  18
    13  21
    14  16
    15  31
    16  20
    17  22
    18  29
    19  19
    20  25
    21  02
    22  05
    23  03
    24  10
    25  15
    26  14
    27  27
    28  06
    29  24
    30  30
    31  23

Answer 1

--this block lists dates that are not in expected format

set serveroutput on;--se sql plus, senao setar o output na ferramenta

declare
  vd date;
begin
  for r in (select data_final from tabela)
    loop
      begin
        vd := to_date(r.data_final,'mm/dd/yyyy');
      exception
        when others then
          dbms_output.put_line('data com erro' || r.data_final);
      end;
    end loop;
end;

At least you’ll have what’s causing the problem