我的TABLE_NAME有一条数据 MIDACT_106_20120325现在我用语句
select * from
(
SELECT TABLE_NAME,to_date(substr(TABLE_NAME,instr(TABLE_NAME,'_',1,2)+1,length(TABLE_NAME)),'yyyy-mm-dd') tdd FROM USER_TABLES
WHERE UPPER(TABLE_NAME) like 'MIDACT_106_%'
)where tdd>=:adate 传入一个日期过滤掉这条数据 但是要报文件和格式不匹配
select * from
(
SELECT TABLE_NAME,to_date(substr(TABLE_NAME,instr(TABLE_NAME,'_',1,2)+1,length(TABLE_NAME)),'yyyy-mm-dd') tdd FROM USER_TABLES
WHERE UPPER(TABLE_NAME) like 'MIDACT_106_%'
)where tdd>=:adate 传入一个日期过滤掉这条数据 但是要报文件和格式不匹配
select * from
(
SELECT TABLE_NAME,to_date(substr(TABLE_NAME,instr(TABLE_NAME,'_',1,2)+1,length(TABLE_NAME)),'yyyy-mm-dd') tdd FROM USER_TABLES
WHERE UPPER(TABLE_NAME) like 'MIDACT_106_%'
) tmpwhere tmp.tdd>=to_date(:adate,'yyyy-mm-dd');
这个查询有问题 应改为
UPPER(TABLE_NAME) like 'MIDACT\_106\_%' escape '\'
SELECT to_date(substr('MIDACT_106_20120325',
instr('MIDACT_106_20120325', '_', 1, 2) + 1,
length('MIDACT_106_20120325')),
'yyyymmdd')
FROM DUAL;然后再试试
传入一个日期过滤掉这条数据应该用<>select * from
(
SELECT TABLE_NAME,to_date(substr(TABLE_NAME,instr(TABLE_NAME,'_',1,2)+1,length(TABLE_NAME)),'yyyymmdd') tdd FROM USER_TABLES
WHERE UPPER(TABLE_NAME) like 'MIDACT_106_%'
)where tdd>:adate or tdd<:adate