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

That is very strange indeed. Run it in another client (something other than sqlplus) and see if you get the same results. — Paul W, Feb 22 '23 at 14:30
Also make sure there isn't a user-defined function, or synonym to some function, named TO_CHAR that is actually being invoked rather than Oracle's built-in function. — Paul W, Feb 22 '23 at 15:22
You might have a corrupt value stored - [as seen here](https://stackoverflow.com/a/12456242/266304), that can look normal with default display, even with modified NLS, but not with explicit `to_char()` even with the same format mask. I believe Toad might show null then too, but can't find a question where that happened. Can you add `dump(start_date, 16)` to your query and add the query and results to your question, as formatted text please, not as an image. — Alex Poole, Feb 22 '23 at 16:15

d r · Answer 1 · 2023-02-22T14:58:10.787

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

Folks can set their own NLS_DATE_FORMAT in their session, and DBAs do sometimes override the default, so the fuller format is not surprising. But the datatype has to be a real date, because if it wasn't, how did the other rows return correct values? — Paul W, Feb 22 '23 at 14:48
@PaulW True, but to_char() works the same, from binary value. NLS sets the display format - doesn't change the type. — d r, Feb 22 '23 at 14:56
@Paul Don't know about Toad ( !? )- maybe. Oracle alone doesn't work like that. — d r, Feb 22 '23 at 15:03
I was referring to your conclusion from the extended format of the first column that it might not actually be stored as a real date. It clearly is, from the correct results seen on 3 out of 4 of the rows. The user's picture is from SQL*Plus. How can it be explained that the TO_CHAR works on all the rows but one? Right now the only thing I can think of is that maybe TO_CHAR isn't Oracle's TO_CHAR but something custom. — Paul W, Feb 22 '23 at 15:20
@PaulW Exactly - there has to be something else involved - it can't be Oracle alone... My answer refferences Oracle dates and formatings. — d r, Feb 22 '23 at 16:05

Alex Poole · Accepted Answer · 2023-02-22T17:06:11.350

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

It would be better to add the query and results to your question, not as a comment. But yes, setting the time bytes to `1,ee,c9` instead of the `1,15,fd` I used [gets the same result](https://dbfiddle.uk/Cs_4dMQq). Even though extract works, you can't be sure the date part is correct, without knowing how it was corrupted. — Alex Poole, Feb 22 '23 at 18:47

Oracle DB Date Field Problem about to_char function

2 Answers2