Oracle DB Date Field Problem about to_char function

Question

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

Answer 1

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;

START_DATE	STR	DMP
11-AUG-2000 00:00:00	11-AUG-2000 00:00:00	Typ=12 Len=7: 78,64,8,b,1,1,1
15-NOV-2008 00:40:04	15-NOV-2008 00:40:04	Typ=12 Len=7: 78,6c,b,f,1,29,5
15-NOV-2008 00:19:56	15-NOV-2008 00:19:56	Typ=12 Len=7: 78,6c,b,f,1,14,39
29-APR-2016 23:42:02	29-APR-2016 23:42:02	Typ=12 Len=7: 78,74,4,1d,18,2b,3

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;

START_DATE	STR	DMP
11-AUG-2000 00:00:00	11-AUG-2000 00:00:00	Typ=12 Len=7: 78,64,8,b,1,1,1
15-NOV-2008 00:40:04	15-NOV-2008 00:40:04	Typ=12 Len=7: 78,6c,b,f,1,29,5
15-NOV-2008 00:20:04	00-000-0000 00:00:00	Typ=12 Len=7: 78,6c,b,f,1,15,fd
29-APR-2016 23:42:02	29-APR-2016 23:42:02	Typ=12 Len=7: 78,74,4,1d,18,2b,3

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

Answer 2

Don't know about Toad, but Oracle DATE datatype is 7 byte binary containing both the date and the time. Oracle sql to_char() function does not return 00 or 000 from that data (invalid number error, probably). It is, most probably, the Varchar2 datatype in your image description (please, don't use images - use text). In case of DATE datatype you would see the format defined by NLS parameters, and I don't think that any DBA has it set to such a full version like in your picture.
How could it be if the original values is of DATE datatype - you can find here:

--  Sample data
WITH
    tbl AS
        (   Select To_Date('11-AUG-2000 00:00:00', 'dd-MON-yyyy hh24:mi:ss') "DATE_DATE" From Dual  Union All
            Select To_Date('15-NOV-2008 00:40:04', 'dd-MON-yyyy hh24:mi:ss') "DATE_DATE" From Dual  Union All
            Select To_Date('15-NOV-2008 00:19:56', 'dd-MON-yyyy hh24:mi:ss') "DATE_DATE" From Dual  Union All
            Select To_Date('29-APR-2016 23:42:02', 'dd-MON-yyyy hh24:mi:ss') "DATE_DATE" From Dual  
        )

SELECT      DATE_DATE,
            EXTRACT(Year From DATE_DATE) "YEAR_FROM_DATE",
            EXTRACT(Month From DATE_DATE) "MONTH_FROM_DATE",
            EXTRACT(Day From DATE_DATE) "DAY_FROM_DATE",
            To_Char(DATE_DATE, 'dd-MON-yyyy hh24:mi:ss') "ALL_FROM_DATE"
FROM        tbl


DATE_DATE YEAR_FROM_DATE MONTH_FROM_DATE DAY_FROM_DATE ALL_FROM_DATE      
--------- -------------- --------------- ------------- --------------------
11-AUG-00           2000               8            11 11-AUG-2000 00:00:00 
15-NOV-08           2008              11            15 15-NOV-2008 00:40:04 
15-NOV-08           2008              11            15 15-NOV-2008 00:19:56 
29-APR-16           2016               4            29 29-APR-2016 23:42:02

... where the first column shows the DATE datatype format in my database and the last column shows full set of date and time contained by DATE datatype

-- examples of to_char() from DATE and Varchar
--  --------------------------------------------------------------
SELECT      To_Char(To_Date('11-AUG-00', 'dd-MON-yy'), 'dd') "DD"
FROM        dual
DD
--
11

--  ---------------------------------------------------
SELECT      To_Char('11-AUG-2000 00:00:00', 'dd') "DD"
FROM        dual
SQL Error: ORA-01722: invalid number