oracle 如何处理 SELECT 语句中的 to_date 异常以忽略这些行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5966274/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to handle to_date exceptions in a SELECT statment to ignore those rows?
提问by ntsue
I have the following query that I am attempting to use as a COMMAND in a crystal report that I am working on.
我有以下查询，我试图将其用作我正在处理的水晶报告中的命令。
SELECT * FROM myTable
WHERE to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}
This works fine, however my only concern is that the date may not always be in the correct format (due to user error). I know that when the to_date function fails it throws an exception.. is it possible to handle this exception in such a way that it ignores the corresponding row in my SELECT statement? Because otherwise my report would break if only one date in the entire database is incorrectly formatted.
这工作正常，但我唯一担心的是日期可能并不总是采用正确的格式（由于用户错误）。我知道当 to_date 函数失败时它会抛出一个异常..是否有可能以忽略我的 SELECT 语句中的相应行的方式处理这个异常？因为否则如果整个数据库中只有一个日期格式不正确，我的报告就会中断。
I looked to see if Oracle offers an isDate function, but it seems like you are supposed to just handle the exception. Any help would be greatly appreciated. Thanks!!
我查看了 Oracle 是否提供了 isDate 函数，但似乎您应该只处理异常。任何帮助将不胜感激。谢谢！！
回答by Justin Cave
Echoing Tony's comment, you'd be far better off storing dates in DATE columns rather than forcing a front-end query tool to find and handle these exceptions.
回应 Tony 的评论，您最好将日期存储在 DATE 列中，而不是强制前端查询工具查找和处理这些异常。
If you're stuck with an incorrect data model, however, the simplest option is to create a function that does the conversion and handles the error,
但是，如果您遇到不正确的数据模型，最简单的选择是创建一个执行转换并处理错误的函数，
CREATE OR REPLACE FUNCTION my_to_date( p_date_str IN VARCHAR2,
p_format_mask IN VARCHAR2 )
RETURN DATE
IS
l_date DATE;
BEGIN
l_date := to_date( p_date_str, p_format_mask );
RETURN l_date;
EXCEPTION
WHEN others THEN
RETURN null;
END my_to_date;
Your query would then become
您的查询将变为
SELECT *
FROM myTable
WHERE my_to_date(myTable.sdate, 'MM/dd/yyyy') <= {?EndDate}
Of course, you'd most likely want a function-based index on the MY_TO_DATEcall in order to make this query reasonably efficient.
当然，您很可能希望在调用时使用基于函数的索引，MY_TO_DATE以使此查询相当有效。
回答by John Hartsock
If your data is not consistent and dates stored as strings may not be valid then you have 3 options.
如果您的数据不一致并且存储为字符串的日期可能无效，那么您有 3 个选项。
- Refactor your DB to make sure that the column stores a date datatype
- Handle the exception of string to date in a stored procedure
- Handle the exception of string to date in a (complex) record selection formula
- 重构您的数据库以确保该列存储日期数据类型
- 处理存储过程中字符串到日期的异常
- 在（复杂的）记录选择公式中处理字符串到日期的异常
I would suggest using the first option as your data should be consistent.
我建议使用第一个选项，因为您的数据应该是一致的。
The second option will provide some flexibility and speed as the report will only fetch the rows that are needed.
第二个选项将提供一些灵活性和速度，因为报告将只获取所需的行。
The third option will force the report to fetch every record in the table and then have the report filter down the records.
第三个选项将强制报告获取表中的每条记录，然后让报告过滤记录。
回答by Michael Schouten
I have the same problem... an old legacy database with varchar fields for dates and decades of bad data in the field. As much as I'd like to, I can't change the datatypes either. But I came up with this solution to find if a date is current, which seems to be what you're doing as well:
我有同样的问题......一个旧的遗留数据库，带有用于日期和数十年不良数据的 varchar 字段。尽我所能，我也无法更改数据类型。但是我想出了这个解决方案来查找日期是否是最新的，这似乎也是您正在做的事情：
select * from MyTable
where regexp_like(sdate, '[0-1][0-9].[0-3][0-9].[0-9][0-9][0-9][0-9]')
-- make sure it's in the right format and ignore rows that are not
and substr(sdate,7,10) || substr(sdate,1,2) || substr(sdate,4,5) >= to_char({?EndDate}, 'YYYYMMDD')
-- put the date in ISO format and do a string compare
The benefit of this approach is it doesn't choke on dates like "February 30".
这种方法的好处是它不会像“2 月 30 日”这样的日期窒息。
回答by Michael Rickman
Since you say that you have "no access" to the database, I am assuming that you can not create any functions to help you with this and that you can only run queries?
既然你说你“无权访问”数据库，我假设你不能创建任何函数来帮助你解决这个问题，你只能运行查询？
If that is the case, then the following code should get you most of what you need with the following caveats: 1) The stored date format that you want to evaluate is 'mm/dd/yyyy'. If this is not the case, then you can alter the code to fit your format. 2) The database does not contain invalid dates such as Feb 30th.
如果是这种情况，那么以下代码应该可以满足您的大部分需求，但需要注意以下几点：1) 您要评估的存储日期格式是“mm/dd/yyyy”。如果不是这种情况，那么您可以更改代码以适合您的格式。2) 数据库不包含无效日期，例如 2 月 30 日。
First, I created my test table and test data:
首先，我创建了我的测试表和测试数据：
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;
Now, the query:
现在，查询：
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');
And my results:
我的结果：
X SDATE
- ----------
2 01/01/1999
The WITH statement will do most of the validating to make sure that the sdate values are at least in the proper format. I had to break out each time unit month / day / year to do the to_date evaluation because I was still getting an invalid month error when I did a to_date on sdate.
WITH 语句将执行大部分验证以确保 sdate 值至少采用正确的格式。我不得不打破每个时间单位月/日/年进行 to_date 评估，因为当我在 sdate 上执行 to_date 时，我仍然收到无效月份错误。
I hope this helps.
我希望这有帮助。
回答by rocky
Trust this reply clarifies...
there is no direct EXCEPTION HANDLER for invalid date.
One easy way is given below once you know the format like DD/MM/YYYY then below given REGEXP_LIKEfunction will work like a charm.
to_date()also will work, when invalid_date is found then cursor will goto OTHERS EXCEPTION. given below.
相信这个回复澄清了......无效日期没有直接的异常处理程序。一旦你知道像 DD/MM/YYYY 这样的格式，下面给出了一种简单的方法，那么下面给定的REGEXP_LIKE函数将像魅力一样工作。
to_date()也可以工作，当找到 invalid_date 时， cursor 将 goto OTHERS EXCEPTION。下面给出。
DECLARE
tmpnum NUMBER; -- (1=true; 0 = false)
ov_errmsg LONG;
tmpdate DATE;
lv_date VARCHAR2 (15);
BEGIN
lv_date := '6/2/2018'; -- this will fail in *regexp_like* itself
lv_date := '06/22/2018'; -- this will fail in *to_date* and will be caught in *exception WHEN OTHERS* block
lv_date := '07/03/2018'; -- this will succeed
BEGIN
tmpnum := REGEXP_LIKE (lv_date, '[0-9]{2}/[0-9]{2}/[0-9]{4}');
IF tmpnum = 0
THEN -- (1=true; 0 = false)
ov_errmsg := '1. INVALID DATE FORMAT ';
DBMS_OUTPUT.PUT_LINE (ov_errmsg);
RETURN;
END IF;
tmpdate := TO_DATE (lv_date, 'DD/MM/RRRR');
--tmpdate := TRUNC (NVL (to_date(lv_date,'DD/MM/RRRR'), SYSDATE));
tmpnum := 1;
EXCEPTION
WHEN OTHERS
THEN
BEGIN
tmpnum := 0;
ov_errmsg := '2. INVALID DATE FORMAT ';
DBMS_OUTPUT.PUT_LINE (ov_errmsg || SQLERRM);
RETURN;
END;
-- continue with your other query blocks
END;
-- continue with your other query blocks
DBMS_OUTPUT.PUT_LINE (tmpnum);
END;
