| Is this a bug or a "feature" [message #299383] |
Mon, 11 February 2008 15:34  |
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  |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #299457 is a reply to message #299441] |
Tue, 12 February 2008 02:13 |
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 |
skooman
Messages: 913
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 #300204 is a reply to message #300193] |
Thu, 14 February 2008 10:38 |
|
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #300231 is a reply to message #299383] |
Thu, 14 February 2008 11:53 |
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 #300244 is a reply to message #299441] |
Thu, 14 February 2008 12:35 |
Frank
Messages: 7901
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 |
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 #300304 is a reply to message #300245] |
Thu, 14 February 2008 23:21 |
Frank
Messages: 7901
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
