Page Comparison

...

This is possible by using the  _MINE_FIX_INVALID_DATE_NEW_VALUE setting.

For exampleExample: 

set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101010101

This is the hex equivalent of 1900-01-01 00:00:00. If the source date is invalid, then the target date will be: 1900.01.01 00:00:00. For an update statement where the invalid date is part of the WHERE clause, the new value of date will also be used.

In the following example END_DATE contained an invalid date value and has been changed to a valid date:

DELETE from AVI."INVALID_DATE"
where (1=1)
and PID = 101
and ORDER_NUMBER = 200
and START_DATE = 2012.09.15 15:24:26
and END_DATE = 1900.01.01 00:00:00

...

Invalid date with valid time component

In the case where the time component is valid, but the date component is not valid, the following can be set:

set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF


This value can be obtained by dumping the target columns - eg. select DUMP(START_DATE,16) from table;

This will keep the time component of the whole date but will only replace the date component with 1900.01.01.

...

dbvrep> set _MINE_FIX_INVALID_DATE_NEW_VALUE = 77640101FFFFFF

...

The MINE process will have to be restarted after setting this to take affect.

...

create table invalid_date (
pid number,
order_number number,
start_date date,
end_date date
);

Now lets insert an invalid date with a correct time component into this table:

export SOURCE_TNS=d112f
sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL
truncate table invalid_date;
declare
   d date;
begin
dbms_stats.convert_raw_value('64640001182e0e', d);
insert into invalid_date values (601, 200, sysdate, d);
insert into invalid_date values (701, 200, sysdate, d);
end;
/
SQL

sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL
declare
   d date;
begin
dbms_stats.convert_raw_value('64640001162f0f', d);
update invalid_date set end_date = d where pid = 601;
end;
/
SQL

sqlplus -s avi/xxx@${SOURCE_TNS}<<SQL
alter session set nls_date_format='YYYY.DD.MM HH24:MI:SS';
select PID, END_DATE from avi.invalid_date;
SQL

PID  END_DATE
---- -------------------
 601 0000.01.00 21:46:14
 701 0000.01.00 23:45:13

TO_CHAR(END_DATE,'Y
-------------------
1900-01-01 21:46:14
1900-01-01 23:45:13
DUMP(END_DATE,16)
--------------------------------------------------------------------------------
Typ=12 Len=7: 77,64,1,1,16,2f,f
Typ=12 Len=7: 77,64,1,1,18,2e,e