错了,是要在where子句中如何写?select Filename as 文件名, ERRORTXT as 文件是否存在 from SAFEERROR where Filename like '%HR%' AND TIME_ID > getdate()比如TIME_ID的值是SMMAL_220_01HR_20110620_014_000.xml,如何取出20110620并和getdate()进行比较呢?
如果文件名格式固定 select Filename as 文件名, ERRORTXT as 文件是否存在 from SAFEERROR where Filename like '%HR%' AND SUBSTR(Filename, 16, 8) > getdate()
SELECT substr('SMMAL_220_01HR_20110620_014_000.xml',instr('SMMAL_220_01HR_20110620_014_000.xml','_',1,3)+1,8) as cdate from dual;CDATE ---------------- 20110620
--自己替换下字符串。
[SYS@myorcl] SQL>SELECT SUBSTR('SMMAL_220_01HR_20110620_014_000.xml',
2 INSTR('SMMAL_220_01HR_20110620_014_000.xml', '_', 1, 3) + 1,
3 INSTR('SMMAL_220_01HR_20110620_014_000.xml', '_', 1, 4) -
4 INSTR('SMMAL_220_01HR_20110620_014_000.xml', '_', 1, 3) - 1) C
5 FROM DUAL
6 ;C
--------
20110620
select Filename as 文件名, ERRORTXT as 文件是否存在 from SAFEERROR where Filename like '%HR%' AND SUBSTR(Filename, 16, 8) > getdate()
----------------
20110620