Oracle Date 10000-01-01

[KY.Chuang]

Hi,
According I knew, Oracle date range is between -4713 and +9999.
Is it possible that Oracle store the vaule : 10000/1/1 of date format in Oracle
Database.
Becuase I saw this value in my data using DBA Studio.
and my application insert this kinds of value into Oracle Database.
From Databse, I got this vaule(10000/1/1) from table field of date format.

Oracle Version : 8.0,5

Thanks & Regards.

K.Y

[Marcel Boogert_1]

Hi there,

if question=oracle
exec metalink.oracle.com
fi

MB.

[Steven E. Protter]

Your back end database is old, but from what you report, the back end database handles five digit years. They probably fixed it when they dealt with Y2K

SEP

[Yogeeraj_1]

hi,

metalink according to note:217143.1, says
date datatype:
range from January 1, 4712 BC to December 31, 9999 AD

the cases that you might have observed is most probably a BUG!

hope this helps!

regards
Yogeeraj

[Eric Antunes]

Hi,

I'm not sure to understand your question but there is no way that you can get this value for the year (at least on a 8.0.5). Because you cannot insert this kind of value, you also cannot get one.

For better understanding, see the explanation for the following error:

"ORA-01841: (full) year must be between -4713 and +9999

Cause: A date specified a year that is not in the valid date range. A valid date is any date between January 1, 4712 B.C. and December 31, 4712 A.D.

Action: Enter a valid date value between 4712 B.C. and 4712 A.D."


Best Regards,

Eric Antunes

[KY.Chuang]

Dear all,
Actually, this kinds of value really exist.
and It's not a bog of Oracle.
Please check the attachment...

Regards.
K.Y

[Yogeeraj_1]

hi,

still looking like a bug in the tool you are using to query the data.

can you post the output of:
select max(t$exdt) from ttiedm100886;


regards
Yogeeraj

[KY.Chuang]

to Yogeeraj,
select max(t$exdt) from ttiedm100886 ;
output data is : 2099/12/31

...
else
Can you give me a sample syntax to export a Table structure and data.
and I will post it to this thread.

K.Y

[KY.Chuang]

to Yogeeraj,
Please check following sql and result,
SQL> select min(t$exdt) from baan.ttiedm100886;
MIN(T$EXDT)
------------
01-JAN-99

SQL> select max(t$exdt) from ttiedm100886;
MAX(T$EXDT)
------------
31-DEC-99

SQL> select min(to_char(T$EXDT, 'YYYY/MM/DD')) from ttiedm100886;

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

K.Y

[Yogeeraj_1]

hi K.Y,

can you post the SQLPLUS output of:
describe ttiedm100886

The last sql output look really intriguing!

thanks
Yogeeraj

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