Oracle SQL: ORA-01858 error on date fields

I have many tables with a date in their name. For example MY_TABLE_2021_06_01, MY_TABLE_2021_06_02, etc. I’m trying to extract the date from the table name and see if any tables are more than an year old. This is my code:

select * from (
  select
      table_name,
to_date(substr(table_name,-2,2)||'/'||substr(table_name,-5,2)||'/'||substr(table_name,-10,4),'DD/MM/YYYY') TABLE_DATE
  from
      all_tables
  where
      owner = 'my_schema'
      and table_name like '%_20%'
)
where
    TABLE_DATE < trunc(sysdate)-365;

The above code works fine if I don’t include the where clause where table_date < trunc(sysdate)-365. If I run the code with the where clause then I get the

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 tried a different and long-winded approach to see if I can get my code to work and it still throws the same error. My new code:

select * from (    
  select table_name, case when table_date < old_date then 1 else 0 end as OLD_TABLE from (
    select
        table_name,
    to_date(substr(table_name,-2,2)||'/'||substr(table_name,-5,2)||'/'||substr(table_name,-10,4),'DD/MM/YYYY') TABLE_DATE,
        trunc(sysdate)-365 OLD_DATE
    from
        all_tables
    where
        owner = 'my_schema'
        and table_name like '%_20%'
    )
 )
where 
    old_table = 1
order by
    old_table desc;

Again the above code works fine if I don’t include where old_table = 1 clause but gives the same ORA-01858 error when the where clause is included. I don’t understand this because the field OLD_TABLE isn’t a date field but still getting a date format error.

Answer

Not all tables that satisfy these conditions:

      owner = 'my_schema'
  and table_name like '%_20%'

have “valid” date format, or it doesn’t match format mask you used.

Note: if table name is MY_TABLE_2021_06_01, why do you TO_DATE(..., 'DD/MM/YYYY')?

2021_06_01 certainly isn’t in DD/MM/YYYY format, but rather YYYY_MM_DD or YYYY_DD_MM (can’t tell, 06 can be both month or day; the same goes for 01). Maybe your code will start working once you fix that.

If not, then list all tables with names like %_20% and see which one(s) of them violate rules you set.

---

Example which works for me: sample tables first:

SQL> CREATE TABLE my_table_2021_06_01
  2  (
  3     id   NUMBER
  4  );

Table created.

SQL> CREATE TABLE some_other_table_2020_02_17
  2  (
  3     id   NUMBER
  4  );

Table created.

Query to extract tables whose “names” are older than 1 year:

SQL> WITH
  2     tables
  3     AS
  4        (SELECT table_name,
  5                TO_DATE (REGEXP_SUBSTR (table_name, 'd+_d+_d+'),
  6                         'yyyy_mm_dd') datum
  7           FROM all_tables
  8          WHERE     owner = 'SCOTT'
  9                AND table_name LIKE '%20%')
 10  SELECT table_name
 11    FROM tables
 12   WHERE datum <= ADD_MONTHS (TRUNC (SYSDATE), -12);

TABLE_NAME
------------------------------
SOME_OTHER_TABLE_2020_02_17

SQL>