How do I search for null date fields using Oracle 11g and TOAD 9.7.2.5?

oops, clicked submit too soon.  I mean maybe one column has blank data and onther is null and maybe it's a different contraint that is failing?

What is the data type of column TestDate?

I've cloned the database by executing the schema generated by the TOAD "Generate Schema Script".
TestDate is a DATE type.

You should recheck the data type of the field, it seems that the data type is string (char, varchar2 etc.).

I've cloned the database by executing the schema generated by the TOAD "Generate Schema Script".
In other words, TestDate is a DATE type in both databases.

also recheck the constraint,  maybe it was a different column that was showing up as null

Let me explain.
I generated a schema script (S1) in TOAD and then executed the script in a blank schema (S2). On the second try, it executed with no errors:
S2 is a clone of S1.

I then copied all of the data by using "Copy data to another schema".

The issue is that there is null data in that field in both of the schemas, because they both have the same schema and data, but the query only works in the cloned (S2) table.

try this on both source and destination


select *
from TestTable
where nvl(TestDate,to_date('99991231235959','yyyymmddhh24miss')) = to_date('99991231235959','yyyymmddhh24miss')


if you happen to have data with that date then pick another one so the nvl will only match your null data

No rows were returned from the source.
Thousands of rows were returned, correctly, from the destination.

I'm perplexed as I can see no data in that column and the below statement also produces the above results:
select *
from TestTable
where trunc(TestDate) is null

How else can you see no data in a DATE type column and it not be null?

you'll need to file a ticket on metalink with oracle support.  
clearly your source system is screwed up.

one last thought,  do you have any rewrite options turned on?  either query rewrite via materialized
views or advanced rewrite that may be running something instead of the SQL you expect?

assuming you don't then I suggest you submit the results of all of the above queries and the following ones to exhaustively show the source sytem's NULL's don't show up.
I wouldn't include anything from the destination system, since it's behaving properly it's not really part of the problem.

select *
from TestTable
minus
select *
from TestTable
where TestDate is not NULL

select count(*) from TestTable;

select count(*) from TestTable where TestDate is not NULL;

select count(*) from TestTable where TestDate is NULL;

another thought.  Is the not null constraint enabled but not validated?

The results should still come back correctly, but that may help indicate the problem if oracle is trying to "optimize" your results by returning nothing for NULLs because it can see a contraint even one that isn't validated.

select *
from TestTable
minus
select *
from TestTable
where TestDate is not NULL------------------- 0 rows

select count(*) from TestTable;------------------- 24077

select count(*) from TestTable where TestDate is not NULL;------------------- 24077

select count(*) from TestTable where TestDate is NULL;------------------- 0

According to TOAD 9.6.1.1's schema browser, the constraint is Enabled and Validated.

how do you know TestTable has thousands of null entries? do you see them in the source system?
or are you only detecting them in the target?

if the latter...
is there any possibility at all that your data copy didn't actually copy everything?  thereby creating null data in the target system?

Ok, I'll put it another way.
This is a DATE column which has a NOT NULL constraint that is Enabled and Validated.
When viewing that table in the schema browser, there are rows in which there is no data in that column.
How can this be?

More to the point, how did I manage to put a NOT NULL constraint on a column with thousands of NULL entries?

you'll have to contact Oracle support for that.

you can put a contraint on it, but it shouldn't have validated.

Can you post all of the DDL for the table and the constraints?
Are you sure the constraint is validated?
Are you sure the data has NULL's?

All of the results you've posted above indicate a system with a constraint that has no bad data.

If that's not the case, you MUST contact Oracle with a "Wrong Results" bug

Interestingly I ran the following SQL:
    select to_char(TestDate , 'dd Mon yyyy')
    from TestTable
    where id = 20000
and it came back with '00 000 0000'
I then ran:
    select count(*)
    from TestTable
    where to_char(TestDate , 'dd Mon yyyy') = '00 000 0000'
and it comes back with a count of all of the rows.

What is date '00 000 0000'? How did I produce it? How would I reproduce it?
Will the following SQL fix it without breaking anything:
    update TestTable
    set TestDate = null
    where to_char(TestDate , 'dd Mon yyyy') = '00 000 0000'

I tried to run:
    update TestTable
    set TestDate = null
    where to_char(TestDate , 'dd Mon yyyy') = '00 000 0000'

and it, quite rightly, will not let me violate the null constraint but why does "Copy data to another schema" try to copy null and not the date: '00 000 0000'?
Is this an Oracle or TOAD problem?

HELP!!
Can anybody tell me how it is possible to have a date field with date '00 000 0000'?

assuming your testtable has just one column (testdate)

the above should create 19 rows of invalid dates and then show them to you like...


TO_CHAR(TESTDATE,'YYYY
----------------------
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00
0000-00-00 00:00:00 00

19 rows selected.