Oracle Date 10000-01-01

[Eric Antunes]

Hi KY.Chuang,

Go to the dirctory where you want your export file, in DOS and execute:

exp80 userid=/@ file=:\...\ttiedm100886.dmp tables=(TTIEDM100886)

In fact the min value for t$exdt is very strange...

Eric

[Eric Antunes]

Can you post the min value without formating with to_char?

select min(t$exdt) from ttiedm100886;

[KY.Chuang]

Dear all,
I already export table structure and data, Please check the attachment...

Following is Log
--------------------------------------------
Connected to: Oracle8 Enterprise Edition Release 8.0.5.0.1 64bit - Production
With the Partitioning and Objects options
PL/SQL Release 8.0.5.0.0 - Production
Export done in ZHT32EUC character set and ZHT32EUC NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table TTIEDM100886 93 rows exported
Export terminated successfully without warnings.
--------------------------------------------

Regards.

K.Y

[Eric Antunes]

Hi KY,

I can't import your table because of the different character set...

Show me the result of:

select min(to_char( t$exdt, 'DD-MM-RRRR')) from ttiedm100886;

[KY.Chuang]

to Eric Antunes,
SQL> select min(to_char(t$exdt,'DD-MM-RRRR'))
2 from ttiedm100886;

MIN(TO_CHA
----------
00-00-0000

K.Y

[Eric Antunes]

K.Y,

Post the results of the following queries from SQL*Plus:

select to_char(min( t$exdt), 'YYYY') from ttiedm100886;

select to_char(min( t$exdt), 'MON') from ttiedm100886;

select to_char(min( t$exdt), 'MM') from ttiedm100886;

select to_char(min( t$exdt), 'DD') from ttiedm100886;

select to_char(min( t$exdt), 'HH:MI:SS') from ttiedm100886;

select to_char(min( t$exdt), 'HH24:MI:SS') from ttiedm100886;

select to_char(min( t$exdt), 'HH') from ttiedm100886;

select to_char(min( t$exdt), 'MI') from ttiedm100886;

select to_char(min( t$exdt), 'SS') from ttiedm100886;

Usually, these kind of issues end up being related to data being entered through some 3rd party application which for some reason does something strange to the date data to cause the error. Is this your case?

Best Regards,

Eric

[KY.Chuang]

To Eric,
the following result as your script...
select to_char(min( t$exdt), 'YYYY') from ttiedm100886;
TO_C
----
0000

select to_char(min( t$exdt), 'MON') from ttiedm100886;
TO_CHA
------
000

select to_char(min( t$exdt), 'MM') from ttiedm100886;
TO
--
00

select to_char(min( t$exdt), 'DD') from ttiedm100886;
TO
--
00

select to_char(min( t$exdt), 'HH:MI:SS') from ttiedm100886;
TO_CHAR(
--------
00:00:00

select to_char(min( t$exdt), 'HH24:MI:SS') from ttiedm100886;
TO_CHAR(
--------
00:00:00

select to_char(min( t$exdt), 'HH') from ttiedm100886;
TO
--
00

select to_char(min( t$exdt), 'MI') from ttiedm100886;
TO
--
00

select to_char(min( t$exdt), 'SS') from ttiedm100886;
TO
--
00

-----------------------------------------
Yes, My agent application process this strange date by itself, and I want to know how it did. But application's company don't want to tell me how it did. So I just can find the solution by myself.
Maybe you or discuss's man have some ideas which can find the solution.
Thanks & Regards.
K.Y

[Eric Antunes · Accepted solution]

Hi K.Y,

Your primary concern should be to correct the data: tell application's company that if they don't want to tell you where the program is doing this, they'll need at least to correct this zero date(s) to the correct(s) value(s).

Eric Antunes

[Don Spare]

The dates in the screen shots appear to have Chinese characters. It is possible that this is a valid Chinese calendar date that fits in the specified Oracle Gregorian calendar range??? I don't know what the offset is but I believe the Chinese calendar is a couple thousand years ahead of the Gregorian one.