Using Oracle Zero Date

Question

I have an application with existing data that has a null value in a date column.

When I look at it from sqlplus, I see: 00-DECEMB

when I use the dump function in this column, I get: Typ = 12 Len = 7: 100,100,0,0,1,1,1,1

I need to work with existing data from .Net (no changes in data, data structure or even existing sql operations)

How to crack this value or write it.

The db version ranges from 8 to 11.

Help will be appreciated

Answer 1

At the end of the day there was no solution to my problem.

What did I do, whenever business logic tried to enter a zero date, I changed it to 1/1/0001, and when I ever got 1/1/0001 or an exception from db, I behaved in business logic, as if i got a zero date.

Answer 2

Not sure what you really expect to achieve, but you can generate raw days through DBMS_STATS.CONVERT_RAW_VALUE.

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 function can help

 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 this in a query like

 select case when date_col = trash_date then null else date_col from table... 

Answer 3

Congratulations, keeper!

Type = 12 Linen = 7: 100,100,0,0,1,1,1,1

Elements in this dump are century, year, month, day, hour, minute, second. So, what you have there is 0-0-00000, which is definitely not a valid date ...

 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> 

change

I used Gary a great trick to break the zero date into a table ....

 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 get around their mind.

I think the only way to do this - and you probably won't like it - is to create an API layer using views ....

 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 most troubling aspect of this is that you will need INSTEAD IF triggers that actually insert null dates into the base table (again, using the Gary function). In addition, to support the same object names, you probably need to build the API in a different scheme.

Therefore, I do not minimize the amount of work that is involved. The problem is that previous developers faced a lot of technical debt with their solution. Now you do not need to pay a whig for this debt (given that you do not want to pay off the capital by rewriting the database).

last news

I just met this funny date in my own environment, which offers an alternative explanation of these funny dates. I added a DATE column to the table in which the rows were. I used the DEFAULT clause to set the default value for sysdate. Guess what happened?

 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, sysdate works as expected for newlines ...

 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> 

Answer 4

As APC found out, you cannot fix this from SQL * PLUS. I ran into a similar problem from the updated jdbc value.

The only solution I could come up with was to UPDATE the row to set the date to a reasonable (if still incorrect) value - you need to reference the row using the primary key (which does not include the corresponding column) or use rowid or do updating in everything where the date column is outside of what seems like a valid range using the same tool that created bad data in the first place (i.e. not sql * plus)

(oh - and try to fix the error that caused the problem!)

NTN

FROM.

Answer 5

This is ridiculously dangerous to do with the way Oracle uses optimizer statistics.

You have an invalid, artificially low date value that is almost certainly used as a NULL surrogate. Oracle does not know that this is a NULL surrogate, just a value, so when it collects optimizer statistics, it will use this invalid date as a low value for the column and assume that the data is linearly distributed within the high and low values ​​that it found, and that 10% of the total data lies above a high value and below a low value.

If you have NULL (missing or invalid) data, write NULL data to the table.