Re: Illegal date format...

Eddie, Andrew, Mark, Peter, et al -

Ouch. I was unpleasantly surprised this morning to find an almost identical example of what started this entire thread. I'm running Oracle 8.1.7.1.1 on WinNT. I'm definitely NOT pulling your leg.

I'm researching a seemingly unrelated performance issue on a box and database that I usually don't deal with. When researching statistics, I
found the following when doing this simple query in SQL*Plus:

SQL> select table_name, last_analyzed from dba_tables where owner = 'PALEO'
  2 and last_analyzed is not null;

TABLE_NAME                     LAST_ANAL 
------------------------------ --------- 
C14_CARIACO0                   00-DECEMB 
MI_URL                         28-SEP-02 

I've left my nls_date_format alone. If I apply a date mask, this is what I get:

SQL> select table_name, to_char(last_analyzed,'mm/dd/yyyy hh24:mi:ss')   2 from dba_tables where owner = 'PALEO' and last_analyzed is not null;

TABLE_NAME                     TO_CHAR(LAST_ANALYZ 
------------------------------ ------------------- 
C14_CARIACO0                   00/00/0000 00:00:00 
MI_URL                         09/28/2002 11:30:05 

This is too weird for words, and I'm _this close_ to declaring a portion of this
database corrupt. I'll continue to research this issue from here (running DBV, etc),
and I'll probably have to export this entire PALEO user, wipe it out, and reimport
it.

My users are definitely not using WebMethods as described earlier in this thread,
and I'm unsure of how the data is being created. My users describe a situation
where a fairly complicated query was running in less than a minute just last
week, and now it takes practically a day to complete. Nothing else has
changed.

Happy day.

TG

shuler_at_ntelos.net (Eddie) wrote in message news:<31c10b07.0209171224.7def9f87_at_posting.google.com>...
> I saw it with my own eyes! Webmethods inserted an illegal date into
> the Oracle database. When I query the table here is what I get. We are
> running database version 7.3.4. And yes the eta_date_lfdat is a date
> field in the databse. Has anyone seen this before? Eddie Shuler
>
> 1* select to_char(eta_date_lfdat,'dd-mon-yyyy') from sap_delivery
> SQL> /
>
> TO_CHAR(ETA_DATE_LFDAT,'DD-MON-YYYY')
> ---------------------------------------------------------------------------
> 00-december-0000
> 00-december-0000
Received on Tue Oct 01 2002 - 11:42:39 CDT