cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Date 10000-01-01

SOLVED
Go to solution
KY.Chuang
Advisor

Oracle Date 10000-01-01

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
Service is King
18 REPLIES
Marcel Boogert_1
Trusted Contributor

Re: Oracle Date 10000-01-01

Hi there,

if question=oracle
exec metalink.oracle.com
fi

MB.
Steven E. Protter
Exalted Contributor

Re: Oracle Date 10000-01-01

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Yogeeraj_1
Honored Contributor

Re: Oracle Date 10000-01-01

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Oracle Date 10000-01-01

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
Each and every day is a good day to learn.
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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

Regards.
K.Y

Service is King
Yogeeraj_1
Honored Contributor

Re: Oracle Date 10000-01-01

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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
Service is King
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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
Service is King
Yogeeraj_1
Honored Contributor

Re: Oracle Date 10000-01-01

hi K.Y,

can you post the SQLPLUS output of:
describe ttiedm100886

The last sql output look really intriguing!

thanks
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Eric Antunes
Honored Contributor

Re: Oracle Date 10000-01-01

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

Each and every day is a good day to learn.
Eric Antunes
Honored Contributor

Re: Oracle Date 10000-01-01

Can you post the min value without formating with to_char?

select min(t$exdt) from ttiedm100886;


Each and every day is a good day to learn.
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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
Service is King
Eric Antunes
Honored Contributor

Re: Oracle Date 10000-01-01

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;

Each and every day is a good day to learn.
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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

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

K.Y
Service is King
Eric Antunes
Honored Contributor

Re: Oracle Date 10000-01-01

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
Each and every day is a good day to learn.
KY.Chuang
Advisor

Re: Oracle Date 10000-01-01

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
Service is King
Eric Antunes
Honored Contributor
Solution

Re: Oracle Date 10000-01-01

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

Each and every day is a good day to learn.
Don Spare
Regular Advisor

Re: Oracle Date 10000-01-01

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.