oracle 忽略异常处理,如何在SELECT语句中处理to_date异常以忽略这些行？

既然你说你对数据库没有“访问权限”，我假设你不能创建任何函数来帮助你，而且你只能运行查询？

如果是这种情况，那么下面的代码应该能够帮助您获得所需的大部分内容，并注意以下事项： 1)您要评估的存储日期格式为'mm/dd/yyyy'。如果情况并非如此，那么您可以更改代码以适合您的格式。 2)数据库不包含无效日期，如2月30日。

首先，我创建了我的测试表和测试数据：

create table test (x number, sdate varchar2(20));

insert into test values (1, null);

insert into test values (2, '01/01/1999');

insert into test values (3, '1999/01/01');

insert into test values (4, '01-01-1999');

insert into test values (5, '01/01-1999');

insert into test values (6, '01-01/1999');

insert into test values (7, '12/31/1999');

insert into test values (8, '31/12/1999');

commit;

现在，查询：

WITH dates AS (

SELECT x

, sdate

, substr(sdate,1,2) as mm

, substr(sdate,4,2) as dd

, substr(sdate,7,4) as yyyy

FROM test

WHERE (substr(sdate,1,2) IS NOT NAN -- make sure the first 2 characters are digits

AND to_number(substr(sdate,1,2)) between 1 and 12 -- and are between 0 and 12

AND substr(sdate,3,1) = '/' -- make sure the next character is a '/'

AND substr(sdate,4,2) IS NOT NAN -- make sure the next 2 are digits

AND to_number(substr(sdate,4,2)) between 1 and 31 -- and are between 0 and 31

AND substr(sdate,6,1) = '/' -- make sure the next character is a '/'

AND substr(sdate,7,4) IS NOT NAN -- make sure the next 4 are digits

AND to_number(substr(sdate,7,4)) between 1 and 9999 -- and are between 1 and 9999

)

)

SELECT x, sdate

FROM dates

WHERE to_date(mm||'/'||dd||'/'||yyyy,'mm/dd/yyyy') <= to_date('08/01/1999','mm/dd/yyyy');

而且我的结果：

X SDATE

- ----------

2 01/01/1999

with语句会做大多数验证是为了确保sdate值至少具有适当的格式。由于我在sdate上执行to_date时仍然收到无效的月份错误，因此我不得不每隔一个单位月/日/年进行一次to_date评估。

我希望这会有所帮助。