Oracle DB Date Field Problem about to_char function

I am sharing screen about my problem. How it can be possible , I really don't understand. Oracle show me date value in column field. But if I want to get day or month value , I just see 00 or 000. I also see that value on Toad NULL value. But it's not null.

Can anyone help me please?

Edited:

select start_date,to_char(start_date, 'DD-MON-YYYY HH24:MI:SS') as str,
  dump(start_date, 16) as dmp,
  extract(day from start_date) day,
  extract(month from start_date) month
from my_table where gsm_no='xxxx';



START_DATE          STR             DMP                          DAY   MONTH
11.08.2000  11-AĞU-2000 00:00:00   Typ=12Len=7:78,64,8,b,1,1,1        11  8
15.11.2008 00:40:04 15-KAS-2008 00:40:04 Typ=12Len=7:78,6c,b,f,1,29,5  15 11
               00-000-0000 00:00:00 Typ=12 Len=7:78,6c,b,f,1,ee,c9     15  11
29.04.2016 23:42:02 29-NIS-2016 23:42:02 Typ=12Len=7:78,74,4,1d,18,2b,3 29  4

It looks like you have a corrupt date value in your table.

With valid dates you wouldn't see this:

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

select start_date,
  to_char(start_date, 'DD-MON-YYYY HH24:MI:SS') as str,
  dump(start_date, 16) as dmp
from your_table;

But if I corrupt the binary value stored for the third value, which can be done with a manipulated hex value:

declare
  d date;
begin
  dbms_stats.convert_raw_value('786c0b0f0115fd', d);
  update your_table set start_date = d
  where start_date = cast(timestamp '2008-11-15 00:19:56' as date);
end;
/

... then now the stored value looks OK at first glance, even when formatted as a string using the session NLS setting; but formatting explicitly - even with the same format string - shows zeros for all of the elements:

select start_date,
  to_char(start_date, 'DD-MON-YYYY HH24:MI:SS') as str,
  dump(start_date, 16) as dmp
from your_table;

fiddle

Your actual dump value might be different of course, I just found one that gave the same apparent value you see.

The explicitly-formatted string version of that corrupted value comes out as 00-000-0000 00:00:00, which is what you are seeing, though you're looking at individual elements. (Interesting that even the month abbreviation is 000 here... And extract() still gives the expected year/month/day numbers, at least with this specific corruption, but might not with yours.)

Some clients might balk at showing it at all, which I suspect is why you see nothing in Toad.

Unless you know how the corruption occurred - which could be from a malformed OCI call, or I seem to recall that legacy imp used to have a bug that could do this - and what the value actually should be you might not be able to correct it properly.

The best you might be able to do is replace it with a valid version of the same apparent date/time by updating that row. But you have no way of knowing if the value is even close to what was intended - I changed the minute and seconds slightly, your corruption could be much worse, and it could be very hard to tell. (Some values might be off slightly, some wildly, some negative...)