title: Oracle SQL: ORA-01858 error on date fields
body: <p>I have many tables with a date in their name. For example <code>MY_TABLE_2021_06_01, MY_TABLE_2021_06_02, etc</code>. 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:</p>
  <pre><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 &lt; trunc(sysdate)-365;
  </code></pre>
  <p>The above code works fine if I don't include the where clause <code>where table_date &lt; trunc(sysdate)-365</code>. If I run the code with the where clause then I get the</p>
  <blockquote>
  <p>ORA-01858: a non-numeric character was found where a numeric was expected
  01858. 00000 -  &quot;a non-numeric character was found where a numeric was expected&quot;
  *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.</p>
  </blockquote>
  <p>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:</p>
  <pre><code>select * from (    
    select table_name, case when table_date &lt; 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;
  </code></pre>
  <p>Again the above code works fine if I don't include <code>where old_table = 1</code> clause but gives the same ORA-01858 error when the where clause is included. I don't understand this because the field <code>OLD_TABLE</code> isn't a date field but still getting a date format error.</p>