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