Need suggestion on selecting part of table_name string in date format and based on that select name of tables , I am using temp table to populate query results to exclude some of the tables and then selecting data from temp tables but somehow I am not getting the desired output when using '<' or '>' from temp table
===============
with t as
(select owner,table_name
from dba_tables where
table_name like 'XXXXXXXX_%' and
table_name not like '%0229%' and
table_name not like '%0230%' and
table_name not like '%0231%' and
table_name not like '%0431%' and
table_name not like '%0631%' and
table_name not like '%0931%' and
table_name not like '%1131%'
Order by owner,table_name
)
SELECT * from t where SUBSTR(table_name,11,8)= TO_CHAR(SYSDATE,'MMDDYYYY');
OWNER TABLE_NAME
------------------------------ ------------------------------
ABC XXXXXXXXX_12042018
Above query is giving desired output but when I use '<' or '>' its just randomly giving data from all years shown in below query where I am trying to extract the part of the table name which is < sysdate but its giving wrong results
====================
with t as
(select owner,table_name
from dba_tables where
table_name like 'XXXXXXXX_%' and
table_name not like '%0229%' and
table_name not like '%0230%' and
table_name not like '%0231%' and
table_name not like '%0431%' and
table_name not like '%0631%' and
table_name not like '%0931%' and
table_name not like '%1131%'
Order by owner,table_name
)
SELECT * from t where SUBSTR(table_name,11,8)<TO_CHAR(SYSDATE,'MMDDYYYY');
OWNER TABLE_NAME
------------------------------ ------------------------------
ABC XXXXXXXXX_01012017
ABC XXXXXXXXX_01012018
ABC XXXXXXXXX_01012019
ABC XXXXXXXXX_01022017
ABC XXXXXXXXX_01022018
ABC XXXXXXXXX_01022019
....
....
...
..
.. more rows
above query should only display tables_name having MMDDYYYY grater than today (12042018). Please suggest.