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

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.

Answer 2

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