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?

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?

• sql
• oracle-database
• select

• 1 Column data type? – jarlh Commented Mar 24 at 14:48
• 2 DId you try NULLIF docs.oracle/en/database/oracle/oracle-database/19/sqlrf/… – Charlieface Commented Mar 24 at 14:50
• @jarlh. It is timestamp(6). – Thorbjørn Ravn Andersen Commented Mar 24 at 15:36
• @Charlieface no. I didn’t know it exists. Thank you fir the pointer – Thorbjørn Ravn Andersen Commented Mar 24 at 15:36
• 1 REPLACE was a bad idea in the first place, because it is a string function, while your xxx column is a timestamp not a string. So, you'd rely on implicit conversions to work exatly how you want them to here. Generally, use CASE WHEN when you want to look at a value and have one result or another based on it. Carlieface is right, though, that Oracle also has a propriatary function NULLIF that can do the same job here. – Thorsten Kettner Commented Mar 24 at 16:43

6 Answers 6

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

You can create a view and use a CASE expression:

CREATE VIEW your_schema.view_name (col1, col2, col3, xxx) AS
  SELECT col1,
         col2,
         col3,
         CASE xxx WHEN TIMESTAMP '0001-01-01 00:00:00.000000' THEN NULL ELSE xxx END
  FROM   other_schema.table_name;

You can also use NULLIF:

CREATE VIEW your_schema.view_name (col1, col2, col3, xxx) AS
  SELECT col1,
         col2,
         col3,
         NULLIF(xxx, TIMESTAMP '0001-01-01 00:00:00.000000')
  FROM   other_schema.table_name;

or DECODE:

CREATE VIEW your_schema.view_name (col1, col2, col3, xxx) AS
  SELECT col1,
         col2,
         col3,
         DECODE(xxx, TIMESTAMP '0001-01-01 00:00:00.000000', NULL, xxx)
  FROM   other_schema.table_name;

fiddle

Yes, creating a view that transparently translates the incorrect timestamp values into NULL is a practical solution. Since you cannot modify the original table, you can define a view where every occurrence of TIMESTAMP '0001-01-01 00:00:00.000000' is replaced with NULL. This can be done using a CASE or DECODE statement. For example:

CREATE VIEW corrected_table AS 
SELECT
      column1,
      column2,
      CASE
          WHEN datetime_column = TIMESTAMP '0001-01-01 00:00:00.000000' THEN NULL 
          ELSE datetime_column
      END AS datetime_column
FROM original_table; 

This approach ensures that whenever you query the view, the incorrect timestamp values will be treated as NULL, allowing your logic to work correctly. If performance is a concern, you could also explore using a virtual column or an ON-THE-FLY transformation in your queries. However, a view is a straightforward and maintainable solution that does not require changes to the original dataset.

I agree with several here that have suggested the NULLIF function.

As a variation on a theme, may I suggest adding a Virtual Column to the table siimilar to (don't have Oeacle handy): -

  alter table [WideWorldImporters].[Sales].[Orders]
  add newCol as (nullif(LastEditedWhen, CAST('2013-01-01 12:00:00.0000000' as DATETIME2)));

Then you could select the new column.

Another option could be Oracle's Row Level Security. Only valid if you wanted to omit or include rows with those dates depending on the user.

So, first of all you need to find all tables and fields owned by a certain user which are timestamps. For that purpose you can use all_tab_columns:

select TABLE_NAME, COLUMN_NAME, DATA_TYPE like '%TIMESTAMP%' as IS_TIMESTAMP
from all_tab_columns
where OWNER = 'thatguy';

then you will have a list of tablename-fieldname pairs.

You can create a view for all tablenames with selecting all fields as they are from the table, except for those whose IS_TIMESTAMP indicates it, for which you do NULLIF(<your expression>).

This behavior stems from the fact that Oracle’s REPLACE function is intended for string manipulation and doesn’t work properly on native TIMESTAMP data. When you try to replace a specific timestamp value, Oracle isn’t converting it to a string, performing a textual replacement, and then converting it back. That’s why—even though it works in simple tests—the cached underlying binary format isn’t altered in your actual queries.

A good solution is to create a view that “transparently” converts that specific bad TIMESTAMP value into a NULL. For example, you could do something like this:

CREATE OR REPLACE VIEW my_fixed_table AS
SELECT
  col1,
  col2,
  CASE 
    WHEN my_timestamp = TIMESTAMP '0001-01-01 00:00:00.000000' THEN NULL 
    ELSE my_timestamp 
  END AS my_timestamp,
FROM your_table;