SQL 如何识别存储在 Oracle DATE 列中的无效（损坏）值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8734540/
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 identify invalid (corrupted) values stored in Oracle DATE columns
提问by spencer7593
Oracle 10.2.0.5
甲骨文 10.2.0.5
What is the easiest way to identify rows in a table that have "invalid" values in DATE columns. By "invalid" here what I mean is a binary representation that violates Oracle rules for date values.
识别表中在 DATE 列中具有“无效”值的行的最简单方法是什么。这里的“无效”是指违反 Oracle 日期值规则的二进制表示。
I recently had an issue with an invalid date stored in a column.
我最近遇到了存储在列中的无效日期的问题。
I was able to use a query predicate to find a particular problematic row:
我能够使用查询谓词来查找特定的有问题的行：
WHERE TO_CHAR(date_expr,'YYYYMMDDHH24MISS') = '00000000000000'
In the case I had, the century byte was invalid...
在我的情况下，世纪字节无效......
select dump(h.bid_close_date) from mytable h where h.id = 54321
Typ=12 Len=7: 220,111,11,2,1,1,1
The century byte should be 100 + two digit century. In this case, there was an extra 100 added, as if the century value was "120", making the year "12011". (The only way I know to get invalid DATE values into the database is using OCI, using native 7-byte DATE representation.)
世纪字节应该是 100 + 两位数的世纪。在这种情况下，添加了额外的 100，就像世纪值是“120”一样，使年份成为“12011”。（我知道将无效 DATE 值输入数据库的唯一方法是使用 OCI，使用本机 7 字节 DATE 表示。）
In this case, the TO_CHAR function returned an identifiable string, which I could use for identifying the wonky DATE value.
在这种情况下，TO_CHAR 函数返回一个可识别的字符串，我可以用它来识别不稳定的 DATE 值。
My question: is there an more general or easier approach (preferably using a SQL SELECT statement) to identify rows with "invalid" values in DATE columns.
我的问题：是否有更通用或更简单的方法（最好使用 SQL SELECT 语句）来识别 DATE 列中具有“无效”值的行。
采纳答案by spencer7593
This identifies invalid months
这标识了无效的月份
SELECT rowid,
pk_column,
DUMP(date_column, 1010) AS dump1
FROM table
WHERE TO_NUMBER(SUBSTR(DUMP(date_column, 1010), INSTR(DUMP( date_column, 1010),
',', 1, 2
) + 1,
INSTR(DUMP(date_column, 1010), ',', 1, 3) - (
INSTR(DUMP( date_column, 1010), ',', 1, 2) + 1
))) = 0;
Update using the same where clause, I found the month number was zero in these cases.
使用相同的 where 子句更新，我发现在这些情况下月份数为零。
回答by APC
This is a pretty unusual scenario (although I have come across something similar once before). The more common problem is finding invalid dates which are held as strings in a date column. You could adapt the solution for that to your situation, by building your own date validator.
这是一个非常不寻常的场景（尽管我以前遇到过类似的事情）。更常见的问题是查找在日期列中作为字符串保存的无效日期。您可以通过构建自己的日期验证器来根据您的情况调整解决方案。
Something like this:
像这样的东西：
create or replace function is_a_date
( p_date in date )
return varchar2
is
d date;
begin
d := to_date(to_char(p_date, 'SYYYYMMDDHH24MISS'), 'SYYYYMMDDHH24MISS') ;
if d != p_date then
return 'not a proper date';
else
return 'good date';
end if;
exception
when others then
return 'not a date';
end;
/
This converts a date into a string and back again. It catches exceptions thrown by date casting. If the end product is not the same as the input date then presumably something got lost in translation; to be honest I'm not sure whether the 12011 date would cast successfully to a string, so this is a belt'n'braces approach. It's a bit tricky writing this utility without some test data!
这会将日期转换为字符串并再次返回。它捕获日期转换引发的异常。如果最终产品与输入日期不同，则可能在翻译中丢失了某些内容；老实说，我不确定 12011 日期是否会成功地转换为字符串，因此这是一种带大括号的方法。在没有一些测试数据的情况下编写这个实用程序有点棘手！
This query would identify all the non-valid dates:
此查询将识别所有无效日期：
select h.id, dump(h.bid_close_date)
from mytable h
where h.bid_close_date is not null
and is_a_date(h.bid_close_date) != 'good date';
回答by spencer7593
Without adding a function, a simple predicate
无需添加函数，一个简单的谓词
TO_CHAR(date_col,'YYYYMMDDHH24MISS') = '000000000000'
appears to be satisfactory to identify corrupted values stored in an Oracle DATE column. The addition of a function appears to be unnecessary. Checking for corrupted dates should be able to be done in a SQL SELECT statement, and not require a user to have CREATE FUNCTION privilege on the database.
识别存储在 Oracle DATE 列中的损坏值似乎令人满意。添加功能似乎是不必要的。检查损坏的日期应该能够在 SQL SELECT 语句中完成，并且不需要用户对数据库具有 CREATE FUNCTION 权限。
回答by Fering
I had an SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0".
我有一个SQL Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0".
So to identify the rows that had a bad date I did the following.
因此，为了识别日期错误的行，我执行了以下操作。
declare
cursor mydates is select table_pk, your_date_col from table;
c_date table.your_date_col%type;
c_pk table.table_pk%type;
testrow table.your_date_col%type;
begin
open mydates;
loop
begin
fetch mydates into c_pk, c_date;
exit when mydates%notfound;
testrow := TO_TIMESTAMP(c_date,'YYYY-MM-DD HH24:MI:SS');
exception when others then
dbms_output.put_line('bad file: ' || c_pk);
end;
end loop;
close mydates;
end;
So all I did was create a cursor, loop through the elements and tested each one and display the identifier so I could easily find the bad rows.
所以我所做的就是创建一个游标，遍历元素并测试每个元素并显示标识符，以便我可以轻松找到坏行。
回答by David Aldridge
I'd go for a method that can be implemented as a check constraint, probably through checking that the date is in the range of legal values.
我会寻找一种可以作为检查约束实现的方法，可能是通过检查日期是否在合法值的范围内。
However, ask yourself also whether it is valid to have a date of 1066-10-14? It is a legal value, but you probably do not have invoices printed on that day, for example. So you might like to roll the invalid date check into a larger issue of what you really consider to be valid in the context of your application.
但是，还要问问自己，日期为 1066-10-14 是否有效？这是一个合法值，但例如，您可能没有当天打印的发票。因此，您可能希望将无效日期检查转化为一个更大的问题，即您真正认为在您的应用程序上下文中有效的内容。
