Home » SQL & PL/SQL » SQL & PL/SQL » Date Bug in Oracle ? (Oracle 10g ; SQL PLUS)
Date Bug in Oracle ? [message #360786] Mon, 24 November 2008 00:49 Go to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

The day difference between two dates ( OCT 15 and OCT 04 ) is strange for the year 1582. I got this quey from one of my friends. Is it an Oracle bug ?

SQL> SELECT TO_DATE('10/15/2008','MM/DD/YYYY')  - TO_DATE('10/4/2008','MM/DD/YYYY') DAY_DIFF
  2  FROM DUAL
  3  ;

  DAY_DIFF
----------
        11

SQL> SELECT TO_DATE('10/15/1582','MM/DD/YYYY')  -  TO_DATE('10/4/1582','MM/DD/YYYY') DAY_DIFF
  2  FROM DUAL ;

  DAY_DIFF
----------
         1

SQL>


If not so , any explanation ... ?

Smile
Rajuvan.

[Updated on: Mon, 24 November 2008 00:50]

Report message to a moderator

Re: Date Bug in Oracle ? [message #360791 is a reply to message #360786] Mon, 24 November 2008 00:55 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
would say, these are the 10 days, cut with the reform of the calendar:
http://en.wikipedia.org/wiki/Gregorian_calendar
Re: Date Bug in Oracle ? [message #360799 is a reply to message #360786] Mon, 24 November 2008 01:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

ok .. Then What about ..


SQL> SELECT TO_DATE('01/01/0001 AD','MM/DD/YYYY AD')  - TO_DATE('12/31/0001 BC','MM/DD/YYYY BC') DAY_DIFF
  2  FROM DUAL ;

  DAY_DIFF
----------
       367

SQL> SELECT TO_DATE('01/01/0002 AD','MM/DD/YYYY AD')  - TO_DATE('12/31/0001 AD','MM/DD/YYYY BC') DAY_DIFF
  2  FROM DUAL ;

  DAY_DIFF
----------
         1

SQL>


Smile
Rajuvan.

[Updated on: Mon, 24 November 2008 01:09]

Report message to a moderator

Re: Date Bug in Oracle ? [message #360803 is a reply to message #360799] Mon, 24 November 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You dig up very very very old stuff.
I think the first time I saw this was in version 5 but I'm pretty sure it would be also asked before in newsgroup when France were not connected.

Year 0 is a leap year. Oracle use theorical Julian calendar for these times.

Regards
Michel
Re: Date Bug in Oracle ? [message #360811 is a reply to message #360786] Mon, 24 November 2008 01:27 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

I was not sure about it , Micheal .

But there is a great difference between Theoretical and practical julian calendar .

Anyway Lets all agree that its not a BUG .

Topic locked.

Smile
Rajuvan.
Re: Date Bug in Oracle ? [message #360828 is a reply to message #360811] Mon, 24 November 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I unlocked the topic, maybe some will have new comments or questions on this issue.

Regards
Michel
Re: Date Bug in Oracle ? [message #360977 is a reply to message #360828] Mon, 24 November 2008 09:02 Go to previous message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Thanks for unlocking the topic Michel.
I was about to add the following link when I found the topic locked. Hope the link makes things interesting.

The Oracle Calender (Thanks to Google and OraFAQ)

Thanks Rajuvan for the post. I learnt something new from this.

Regards,
Jo
Previous Topic: ORA-01722: invalid number in 10g
Next Topic: Numeric data from Character colomn and filter again with condtion
Goto Forum:
  


Current Time: Fri Dec 09 02:14:29 CST 2016

Total time taken to generate the page: 0.13485 seconds