Using Oracle Zero Date

Question

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

when I use the dump function on this column, I Get: 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)

How the hack do I read this value, or write it.

The db version varies, from 8 to 11.

Help would be appreciated

Answer 1

Congratulations, that's a keeper!

Typ=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...

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 ....

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....

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.

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).

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?

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...

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 2

As APC found out you can't fix this from SQL*PLUS. I came across a similar problem from a jdbc updated value.

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)

(oh - and try to fix the bug which caused the problem!)

HTH

C.

Answer 3

Not sure what you actually expect to achieve, but you can generate 'cruddy' dayes 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 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...

Answer 4

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

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.

If you've got NULL (absent or invalid) data, write NULL data to the table.

Answer 5

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.