topic Re: Oracle Date 10000-01-01 in Operating System - HP-UX

Oracle Date 10000-01-01

KY.Chuang — Thu, 23 Sep 2004 00:10:06 GMT

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

Re: Oracle Date 10000-01-01

Marcel Boogert_1 — Thu, 23 Sep 2004 00:44:50 GMT

Hi there,

if question=oracle
exec metalink.oracle.com
fi

MB.

Re: Oracle Date 10000-01-01

Steven E. Protter — Thu, 23 Sep 2004 00:56:56 GMT

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

Re: Oracle Date 10000-01-01

Yogeeraj_1 — Thu, 23 Sep 2004 03:32:48 GMT

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

Re: Oracle Date 10000-01-01

Eric Antunes — Thu, 23 Sep 2004 03:45:46 GMT

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

Re: Oracle Date 10000-01-01

KY.Chuang — Thu, 23 Sep 2004 22:51:51 GMT

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

Regards.
K.Y

Re: Oracle Date 10000-01-01

Yogeeraj_1 — Fri, 24 Sep 2004 01:09:27 GMT

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

Re: Oracle Date 10000-01-01

KY.Chuang — Fri, 24 Sep 2004 01:44:09 GMT

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

Re: Oracle Date 10000-01-01

KY.Chuang — Fri, 24 Sep 2004 02:10:28 GMT

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

Re: Oracle Date 10000-01-01

Yogeeraj_1 — Fri, 24 Sep 2004 03:26:52 GMT

hi K.Y,

can you post the SQLPLUS output of:
describe ttiedm100886

The last sql output look really intriguing!

thanks
Yogeeraj

Re: Oracle Date 10000-01-01

Eric Antunes — Fri, 24 Sep 2004 03:49:03 GMT

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

Re: Oracle Date 10000-01-01

Eric Antunes — Fri, 24 Sep 2004 03:51:04 GMT

Can you post the min value without formating with to_char?

select min(t$exdt) from ttiedm100886;

Re: Oracle Date 10000-01-01

KY.Chuang — Fri, 24 Sep 2004 04:05:46 GMT

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

Re: Oracle Date 10000-01-01

Eric Antunes — Fri, 24 Sep 2004 06:05:37 GMT

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;

Re: Oracle Date 10000-01-01

KY.Chuang — Tue, 28 Sep 2004 20:25:42 GMT

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

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

K.Y

Re: Oracle Date 10000-01-01

Eric Antunes — Wed, 29 Sep 2004 06:10:02 GMT

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

Re: Oracle Date 10000-01-01

KY.Chuang — Wed, 29 Sep 2004 20:44:50 GMT

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

Re: Oracle Date 10000-01-01

Eric Antunes — Thu, 30 Sep 2004 03:57:39 GMT

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

Re: Oracle Date 10000-01-01

Don Spare — Wed, 06 Oct 2004 10:18:28 GMT

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.