使用 Oracle 零日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2086175/
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
Using Oracle Zero Date
提问by Noam
I have an application with existing data, that has Zero in the date column.
我有一个包含现有数据的应用程序，它在日期列中为零。
When I look at it from sqlplus I see: 00-DECEMB
当我从 sqlplus 查看它时，我看到： 00-DECEMB
when I use the dump function on this column, I Get: Typ=12 Len=7: 100,100,0,0,1,1,1
当我在此列上使用转储功能时，我得到： Typ=12 Len=7: 100,100,0,0,1,1,1
I need to work with the existing data from .Net (no changes to the data,or the data structure or even existing sql statements)
我需要使用 .Net 中的现有数据（不更改数据、数据结构甚至现有 sql 语句）
How the hack do I read this value, or write it.
我如何读取或写入此值。
The db version varies, from 8 to 11.
db 版本从 8 到 11 不等。
Help would be appreciated
帮助将不胜感激
采纳答案by Noam
At the end of the day, there was no solution to my problem.
在一天结束时，我的问题没有解决方案。
What I did was, whenever the business logic tried to enter a zero date, i changed it to 1/1/0001 and when ever i got 1/1/0001 or an exception from the db, I behaved in the business logic as if i got zero date.
我所做的是，每当业务逻辑尝试输入零日期时，我将其更改为 1/1/0001，并且当我收到 1/1/0001 或来自数据库的异常时，我在业务逻辑中的行为就好像我的日期为零。
回答by Gary Myers
Not sure what you actually expect to achieve, but you can generate 'cruddy' dayes through DBMS_STATS.CONVERT_RAW_VALUE.
不确定您实际期望实现的目标，但您可以通过 DBMS_STATS.CONVERT_RAW_VALUE 生成 'cruddy' dayes。
create or replace function stats_raw_to_date (p_in raw) return date is
v_date date;
v_char varchar2(25);
begin
dbms_stats.CONVERT_RAW_VALUE(p_in, v_date);
return v_date;
exception
when others then return null;
end;
/
select x, dump(x) y from (select stats_raw_to_date('64640000010101') x from dual);
So what may help is a function
那么可能有帮助的是一个函数
create or replace function trash_date return date deterministic is
v_date date;
begin
dbms_stats.CONVERT_RAW_VALUE('64640000010101', v_date);
return v_date;
end;
/
Then you can use that in a query like
然后你可以在像这样的查询中使用它
select case when date_col = trash_date then null else date_col
from table...
回答by APC
Congratulations, that's a keeper!
恭喜，是守门员！
Typ=12 Len=7: 100,100,0,0,1,1,1
典型值=12 Len=7：100,100,0,0,1,1,1
The elements in that dump are century, year, month, day, hour minute second. So what you have there is midight on 0-0-0000, which is definitely not a valid date...
该转储中的元素是世纪、年、月、日、时分秒。所以你所拥有的是 0-0-0000 的午夜，这绝对不是一个有效的日期......
SQL> create table d (d1 date)
2 /
Table created.
SQL> insert into d values (to_date('00-00-0000', 'dd-mm-yyyy'))
2 /
insert into d values (to_date('00-00-0000', 'dd-mm-yyyy'))
*
ERROR at line 1:
ORA-01847: day of month must be between 1 and last day of month
SQL>
edit
编辑
I used Gary's excellent trick to crowbar a zero date into a table ....
我使用 Gary 的绝妙技巧将零日期撬入表格中......
SQL> select * from d
2 /
D1
---------
00-DECEMB
19-JAN-10
SQL>
So at least we know how your "Magic" developers did it. Now all we have to do is work around their cleverness.
所以至少我们知道你们的“魔法”开发者是如何做到的。现在我们要做的就是解决他们的聪明才智。
I think the only way to do this - and you're probably not going to like it - is to build an API layer, using views....
我认为这样做的唯一方法 - 你可能不会喜欢它 - 是构建一个 API 层，使用视图......
SQL> create or replace view v_d as
2 select case when d1 = trash_date then null else d1 end as d1
3 from d
4 /
View created.
SQL> select * from v_d
2 /
D1
---------
19-JAN-10
SQL>
Not the least disturbing aspect of this is that you will need to have INSTEAD IF triggers which actually insert zero dates into the underlying table (again, using Gary's function). Plus, in order to maintain the same object names you'll probably need to build the API in a different schema.
最不令人不安的方面是，您将需要 INSTEAD IF 触发器，它实际上将零日期插入基础表中（再次使用 Gary 的函数）。另外，为了维护相同的对象名称，您可能需要在不同的模式中构建 API。
So I am not minimising the amount of work which is involved. The problem is, the previous developers ran up a lot of technical debt with their solution. Now it is down to you to pay the vig on that debt (given that you do not want to pay off the capital by rewriting the database).
所以我没有尽量减少所涉及的工作量。问题是，以前的开发人员在他们的解决方案中承担了很多技术债务。现在由您来支付债务的 vig（假设您不想通过重写数据库来偿还资本）。
late breaking news
迟到的突发新闻
I have just come across this funny date in my own environment, which offers an alternative explanation for these funny dates. I added a DATE column to a table which had rows. I used the DEFAULT clause to set a default value to sysdate. Guess what happened?
我刚刚在我自己的环境中遇到了这个有趣的约会，它为这些有趣的约会提供了另一种解释。我在有行的表中添加了一个 DATE 列。我使用 DEFAULT 子句将默认值设置为 sysdate。猜猜发生了什么？
SQL> select * from t69
2 /
ID
----------
1
2
SQL> alter table t69 add col2 date default sysdate not null
2 /
Table altered.
SQL> select * from t69
2 /
ID COL2
---------- ---------
1 00-DECEMB
2 00-DECEMB
SQL>
For the record, the sysdate works as expected for new rows...
作为记录， sysdate 对新行按预期工作......
SQL> insert into t69 (id) values (3)
2 /
1 row created.
SQL> select * from t69
2 /
ID COL2
---------- ---------
1 00-DECEMB
2 00-DECEMB
3 28-APR-10
SQL>
回答by symcbean
As APC found out you can't fix this from SQL*PLUS. I came across a similar problem from a jdbc updated value.
正如 APC 发现的那样，您无法通过 SQL*PLUS 解决此问题。我从 jdbc 更新值中遇到了类似的问题。
The only solution I could come up with was to UPDATE the row to set the date to a sensible (if still incorrect) value - you need to reference the row using a primary key (which does not include the column in question) or use the rowid or do an update on everything where the date column is outside what appears to be a valid range using the same kind of tool which created the bad data in the first place (i.e. not sql*plus)
我能想出的唯一解决方案是更新行以将日期设置为合理的（如果仍然不正确）值 - 您需要使用主键（不包括相关列）引用行或使用rowid 或使用相同类型的工具对日期列超出有效范围的所有内容进行更新，该工具首先创建了错误数据（即不是 sql*plus）
(oh - and try to fix the bug which caused the problem!)
（哦 - 并尝试修复导致问题的错误！）
HTH
HTH
C.
C。
回答by Adam Musch
This is ridiculously dangerous to do with how Oracle uses optimizer statistics.
这与 Oracle 使用优化器统计信息的方式有关，这是非常危险的。
You've got an invalid, artificially low date value which is almost certainly being used as a NULL surrogate. Oracle doesn't know that it's a NULL surrogate, just a value, so when it gathers optimizer statistics, it'll use that invalid date as the low value for the column, and assume that the data is linearly distributed within the high and low values it's found, and that 10% of the data total lies above the high value and below the low value.
您有一个无效的、人为的低日期值，几乎可以肯定它被用作 NULL 代理。Oracle 不知道它是一个 NULL 代理，只是一个值，所以当它收集优化器统计信息时，它会使用那个无效日期作为列的低值，并假设数据在高和低范围内线性分布找到的值，并且总数据的 10% 位于高值之上和低值之下。
If you've got NULL (absent or invalid) data, write NULL data to the table.
如果您有 NULL（不存在或无效）数据，请将 NULL 数据写入表中。
