Had an interesting little case come up today when helping a client re-write a view. This legacy application stored a date value as January 1, 1970 when they didn't have a date (basically using it as a de facto NULL). I did my best to lecture the client on why this was a horrible idea, but there was nothing we could do in the short term.

Anyway, he had a view that would use a DECODE function to convert a date of 1970/01/01 to NULL, otherwise just pass the date through. However, we noticed that the view schema said the field was defined as varchar2(18) instead of date. Here is my example using a view on DBA_OBJECTS:

SQL> create or replace view test_objects1 (object_name, created) as
  2  select object_name, decode (created
  3                          , to_date('1970/01/01','yyyy/mm/dd')
  4                          , null, created)
  5  from dba_objects;

View created.

SQL>
SQL> desc test_objects1;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 OBJECT_NAME                                        VARCHAR2(128)
 CREATED                                            VARCHAR2(18)



I suggested we wrap that NULL in a to_date function just to see what happens, and sure enough that gives us what we want:

SQL> create or replace view test_objects2 (object_name, created) as
  2  select object_name, decode (created
  3                          , to_date('1970/01/01','yyyy/mm/dd')
  4                          , to_date(null), created)
  5  from dba_objects;

View created.

SQL>
SQL> desc test_objects2;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 OBJECT_NAME                                        VARCHAR2(128)
 CREATED                                            DATE

It seems silly to wrap NULL in a TO_DATE, or do any function on a NULL value. One would hope that Oracle sees the NULL and short-circuits the function to avoid doing the unnecessary work.

Update: Looks like this is documented functionality in the DECODE function:
Oracle automatically converts expr and each search value to the datatype of the first search value before comparing. Oracle automatically converts the return value to the same datatype as the first result. If the first result has the datatype CHAR or if the first result is null, then Oracle converts the return value to the datatype VARCHAR2.

Update #2: Thanks to Sayan in the comments for tipping me off about NULLIF, which seems like the best option, doing exactly what we want without having to convert the type:

 SQL> create or replace view test_objects3 (object_name, created) as
  2  select object_name
  3          , nullif (created
  4                      , to_date('1970/01/01','yyyy/mm/dd'))
  5  from dba_objects;

View created.

SQL>
SQL> desc test_objects3;
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------
 OBJECT_NAME                                        VARCHAR2(128)
 CREATED                                            DATE