Handling bad date time value transparently as null in select?

I have an Oracle database table I cannot edit where - due to miscommunication - datetime values are ‘01-01-01 00:00:00,000000’ instead of null. I cannot change the data and it is too huge to make a copy.

I have found that

replace(XXX, TIMESTAMP ‘0001-01-01 00:00:00.000000’, null) 

Works in synthetic selects but it doesn’t work well in actual selects leaving the value breaking my logic of calculating offsets etc. Is this because timestamps cannot be replaced like this?

What I really would like to do is have Oracle transparently translate this value into null wherever it's seen but I don't know how.

How can I solve this? Would a view of the table (in another tablespace I can control) be a feasible solution?

Answer

You want NULL in case the timestamp is 0001-01-01 at midnight. You can use a CASE expression for such things (which has been both in standard SQL and Oracle for ages):

CASE WHEN xxx <> DATE '0001-01-01' THEN xxx ELSE NULL END

The ELSE NULL in this expression is optional, because NULL is the default in CASE expressions that have no match:

CASE WHEN xxx <> DATE '0001-01-01' THEN xxx END

Oracle also supports the now standard function NULLIF (which you may prefer for its brevity):

NULLIF(xxx, DATE '0001-01-01')