Home » SQL & PL/SQL » SQL & PL/SQL » Is this a bug or a "feature"
Is this a bug or a "feature" [message #299383] Mon, 11 February 2008 15:34 Go to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
This feels like a bug to me, but Ive never seen it before and ive been playing around with 99990101 for many years (although I never tried to add a year to it, one of our coders did that).

Can you try this:-

select add_months(to_date('99990101','YYYYYMMDD'),12) from dual;


I get 00/00/0000 which is an invalid date. It goes into a DATE field in Oracle too.

Then I cant reference the date using any to_date functionality, I can only reference the date using

select *
from table
where to_char(date,'YYYYMMDD') = '00000000';


Oddly, if you do this, you get the error I would have expected in the first place

select add_months(to_date('99990101','YYYYYMMDD'),24) from dual;

[Updated on: Mon, 11 February 2008 16:15]

Report message to a moderator

Re: Is this a bug or a "feature" [message #299432 is a reply to message #299383] Tue, 12 February 2008 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If I execute your first statement, I get:
SQL> select add_months(to_date('99990101','YYYYYMMDD'),12) from dual;
select add_months(to_date('99990101','YYYYYMMDD'),12) from dual
                                     *
ERROR at line 1:
ORA-01812: year may only be specified once

If I remove one Y, I get:
SQL> select add_months(to_date('99990101','YYYYMMDD'),12) from dual;
ERROR:
ORA-01801: date format is too long for internal buffer

no rows selected

If I executed your last statement I get:
SQL> select add_months(to_date('99990101','YYYYYMMDD'),24) from dual;
select add_months(to_date('99990101','YYYYYMMDD'),24) from dual
                                     *
ERROR at line 1:
ORA-01812: year may only be specified once

When I remove one Y I get:
SQL> select add_months(to_date('99990101','YYYYMMDD'),24) from dual;
select add_months(to_date('99990101','YYYYMMDD'),24) from dual
                          *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Version:
SQL> @v

Version Oracle : 10.2.0.3.0

Could you post as I did?
But it is clear there is a bug around year 9999 but this is not the first one. There are many bugs in date processing.

By the way, returning 00/00/0000 is the way Oracle found to say it encounters a bug in the date data.

Regards
Michel
Re: Is this a bug or a "feature" [message #299441 is a reply to message #299383] Tue, 12 February 2008 01:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
In my opininion, using the year 9999 is a bigger bug than the bug described. (unless you are managing some sort of SciFi database)
Re: Is this a bug or a "feature" [message #299457 is a reply to message #299441] Tue, 12 February 2008 02:13 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Sorry,

Too many Y's in my haste.

This is what I get:-

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 
Connected as my_user
 
SQL> 
SQL> select add_months(to_date('99990101','YYYYMMDD'),12) from dual;
 
ADD_MONTHS(TO_DATE('99990101',
------------------------------
00/00/0000
SQL> select add_months(to_date('99990101','YYYYMMDD'),24) from dual;
 
select add_months(to_date('99990101','YYYYMMDD'),24) from dual
 
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
 
SQL> 

[Updated on: Tue, 12 February 2008 02:13]

Report message to a moderator

Re: Is this a bug or a "feature" [message #300157 is a reply to message #299457] Thu, 14 February 2008 07:59 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
From the concepts guide:

"Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era, or 'AD'). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default."
(okay, for the neurotic types among us: is says "can store", which doens't have to apply to "can do some SQL with it", but still, I think the message is pretty clear).

So, using 999 as a year you're lucky to get any result at all, and not some ORA message.
Re: Is this a bug or a "feature" [message #300160 is a reply to message #300157] Thu, 14 February 2008 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sabine,
your documentation is out of date.
Oracle supports years until 31/12/9999 since 8i.

Regards
Michel
Re: Is this a bug or a "feature" [message #300164 is a reply to message #300160] Thu, 14 February 2008 08:12 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Haha, then it is Seriously out of date.
However, I copied it straight from the concept guide of 10 release 2: http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#sthref3828
Re: Is this a bug or a "feature" [message #300174 is a reply to message #300164] Thu, 14 February 2008 08:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Shame on them!
From SQL Reference 8.1.5: http://download.oracle.com/docs/cd/F49540_01/DOC/server.815/a67779/ch2.htm#3463
Quote:
DATE
Valid date range from January 1, 4712 BC to December 31, 9999 AD.

Regards
Michel
Re: Is this a bug or a "feature" [message #300178 is a reply to message #300174] Thu, 14 February 2008 08:52 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay, but still: adding 12 months to a date 01019999 is not supported. Not a bug, a documented feature.
Re: Is this a bug or a "feature" [message #300180 is a reply to message #300178] Thu, 14 February 2008 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The bug is that the error should be 1841 each time and not 1801 from time to time.

Regards
Michel
Re: Is this a bug or a "feature" [message #300193 is a reply to message #300180] Thu, 14 February 2008 09:39 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Okay. Raise an SR?
Re: Is this a bug or a "feature" [message #300204 is a reply to message #300193] Thu, 14 February 2008 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want me to raise a SR?
Why not, it is one more on date I will have.
Most of them are not fixed. It seems Oracle does not think bugs on date datatype are urgent and I don't think bug on year 9999 will be.

Regards
Michel

[Updated on: Thu, 14 February 2008 10:55]

Report message to a moderator

Re: Is this a bug or a "feature" [message #300206 is a reply to message #300204] Thu, 14 February 2008 10:42 Go to previous messageGo to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
If I would have to set priorities, fixing something that becomes a serious issue in the year 9999 would not be in my top 10. Laughing

Actually, I meant that the OP could raise an SR if all this bothers him/her. But if you feel like it, please go ahead.
Re: Is this a bug or a "feature" [message #300231 is a reply to message #299383] Thu, 14 February 2008 11:53 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Thanks for the investigation guys.

I would say that 9999 is definateley largely used on "open ended" records where the stop date is nto set.

Infact, we have a system which was written during Oracle 7, and some tables use 4712 as their high date, and some use 9999!

As well as the conversion from CHAR to VARCHAR b ack in 98 not stripping off trailing spaces, so "some" of the fields are space padded and the online screens cant cope lol. Ahh the joys.

Anyway, thanks again.
Re: Is this a bug or a "feature" [message #300243 is a reply to message #300231] Thu, 14 February 2008 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I would say that 9999 is definateley largely used on "open ended" records where the stop date is nto set.

This is a very bad practice that fools the optimizer.
Never do that.
NULL is made for that.

Regards
Michel
Re: Is this a bug or a "feature" [message #300244 is a reply to message #299441] Thu, 14 February 2008 12:35 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Frank wrote on Tue, 12 February 2008 08:11
In my opininion, using the year 9999 is a bigger bug than the bug described. (unless you are managing some sort of SciFi database)

Re: Is this a bug or a "feature" [message #300245 is a reply to message #299383] Thu, 14 February 2008 13:01 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
If only we could all redesign systems we never built eh.

however, I personally think 9999 (or another high date) is better than null in certain circumstances Although not all.

Many ways to skin a cat etc...

1. when you want to use an index to find the "open" records (say there were many hundreds for a particular key value only separated by end_date. Nulls are not stored in indexes the last time I checked. so you would be reading all values in the index to fund the null ones.

2. when you want end_date as part of a primary key. (ie there can only be ONE open record for example).

Re: Is this a bug or a "feature" [message #300247 is a reply to message #300245] Thu, 14 February 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1. Use FBI and gives NULL a value if you often query this kind of records.

2. Does end_part must be part of a primary key? Is this that identify your row in your model? Don't you just need a unique index?

Regards
Michel
Re: Is this a bug or a "feature" [message #300304 is a reply to message #300245] Thu, 14 February 2008 23:21 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
1: agree with Michel. Create a function based index on it.
2: If it were part of the primary key, you would not be able to update it.

But of course you are right about 'inheriting' systems, and not always being able to change what we stumble upon.
Previous Topic: Replacing Special Character
Next Topic: MERGE STATEMENTS
Goto Forum:
  


Current Time: Thu Dec 08 21:48:13 CST 2016

Total time taken to generate the page: 0.08307 seconds