Re: DIGO (Date in Garbage Out?)

A copy of this was sent to cdeanhardt_at_my-deja.com (if that email address didn't require changing) On Wed, 03 Nov 1999 00:17:10 GMT, you wrote:

>Has anyone seen this? We've got a call in to
>Oracle on it, they should be getting back with us
>sometime this year...
>
>Script ONE (below) inserts a sequential series of
>dates into the database. Oracle's TO_DATE
>function seems to *CHANGE* all the dates between
>05-OCT-1582 and 14-OCT-1582 to 15-OCT-1582. I've
>seen this in 8.0.5, 8.1.5.1, and 7.3.3. It
>shouldn't be because of the change from Julian to
>Gregorian calendars, since that was 1752.
>
>Script TWO (below) checks all days (except leap
>days) from 1200 to 1999 to see where else it might
>happen. Only these 10 dates get mucked with.
>
>Clif Deanhardt
>

There was a historical change from the Julian calendar to the Gregorian calendar in 1582. The problem was that the Julian calendar did not accurately account for leap years so when the switch occurred some days had to be eliminated from the calendar to account for this problem. The days October 5 to October 14, 1582 were eliminated. This change was not immediately adopted by every country in the world. Oracle accounts for the change in 1582. Apparently, the decision made by the developers of the Unix 'cal' application is to make the calendar conversion in 1752 which is when England switched to the Gregorian calendar (and they eliminated 11 days in 1752 when they switched).

http://musedata.stanford.edu/hcal/hcal.html

http://www.bio.umass.edu/biology/conn.river/calendar.html

http://www.infoplease.com/ce5/CE008522.html

http://marshal.uwyo.edu/year2000/faq2.htm#4

>--- SCRIPT ONE -----
>drop table test_date;
>Create table test_date ( test date);
>insert into test_date values (to_date('03-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('04-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('05-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('06-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('07-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('08-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('09-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('10-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('11-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('12-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('13-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('14-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('15-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('16-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>insert into test_date values (to_date('17-OCT-1582
>01:00:00','DD-MON-YYYY HH24:MI:SS'));
>set linesize 150
>col dmp format a40
>col dmp2 format a40
>select test, to_char(test,'YYYYMMDD HH24MISS'),
>dump(test,16) dmp, dump(test) dmp2 from test_date;
>
>
>--- SCRIPT TWO ----------
>declare
> ADay Date;
> bPrevFailed Boolean := FALSE;
> iDay Integer;
> iMonth Integer;
> iYear Integer;
>
> procedure CheckOne(iDay integer,
> iMonth integer,
> iYear integer ) is
> cDate varchar2(20);
> begin
> cDate := lpad(iDay,2,'0') || '-' ||
>lpad(iMonth,2,'0') || '-' || lpad(iYear,4,'0');
> if
>to_char(to_date(cDate,'DD-MM-YYYY'),'DD-MM-YYYY')
><> cDate then
> dbms_output.put_line(cDate || '
>Translated to ' ||
>to_char(to_date(cDate,'DD-MM-YYYY'),'DD-MM-YYYY'))
>;
> end if;
> exception
> when others then
> dbms_output.put_line('Trying: ' ||
>cDate);
> raise;
> end;
>
>begin
> dbms_output.enable(1000000);
> for iYear in 1200 .. 1999 loop
> for iMonth in 1 .. 12 loop
> if iMonth in (1,3,5,7,8,10,12) then
> for iDay in 1 .. 31 loop
> CheckOne(iDay,iMonth,iYear);
> end loop;
> elsif iMonth in (4,6,9,11) then
> for iDay in 1 .. 30 loop
> CheckOne(iDay,iMonth,iYear);
> end loop;
> else
> for iDay in 1 .. 28 loop
> CheckOne(iDay,iMonth,iYear);
> end loop;
> end if;
> end loop;
> end loop;
>end;
>/
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Nov 03 1999 - 08:03:45 CST