Oracle Date Error - ORA-01841

I have the following table in Oracle11g.

SQL> DESC tmp_test;
Name                 Type          Nullable Default Comments 
-------------------- ------------- -------- ------- -------- 
SERNO                NUMBER(10)                              
CARDNO               VARCHAR2(25)  Y                         
COL_A                VARCHAR2(255) Y                         
DATEA                DATE          Y                         
DATEB                DATE          Y                         
TAG                  VARCHAR2(255) Y                         
FEEDBACK             CHAR(1)       Y


SQL> 
SQL> SELECT * FROM (SELECT T.COL_A FROM TEMP_TEST T ORDER BY DBMS_RANDOM.VALUE) WHERE ROWNUM <=10;
COL_A
--------------------------------------------------------------------------------
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150205-0000
00 OK.20150301-0000
00 OK.20150301-0000
00 OK.20150213-0000
00 OK.20150301-0000
00 OK.20150129-0000
00 OK.20150301-0000
10 rows selected

SQL>

I am attempting to identify all rows in table TEMP_TEST where DATE in COL_A is less than SYSDATE - 7.

SQL>
SQL> SELECT * FROM TEMP_TEST T WHERE
TO_DATE(SUBSTR(TRIM(T.COL_A),7,8),'YYYYMMDD') < sysdate-7;
**ORA-01841: (full) year must be between -4713 and +9999, and not be 0**
SQL>

The table only contain 200 rows, so I have visually checked for any issues with the data. All dates are valid. What might be the cause of this error?

Thanks

Solution

Apparently all dates are not valid, hence the error you're getting. I would try something like the following (untested, but think it's ok), just to identify the problem records.

declare 
  v_date date;
begin
  for c in (select col_a from temp_test) loop
    begin
      v_date := to_date(substr(trim(c.col_a),7,8),'YYYYMMDD');
    exception when others then
      dbms_output.put_line(c.col_a);
    end;
  end loop;
end;

Note that this use of others as the only exception handler would generally be considered poor practice. In production code, exceptions should be handled individually. Even when used for debugging, it would be better to output the SQL error, but for a first pass where only a few errors are expected, sometimes it's ok just to be lazy.