Re: How does one enter dates beyond the implicit, Oracle internal date limits?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Sat, 30 Jan 2010 05:49:03 -0600
Message-ID: <7b8774111001300349o1c0546f8hd74f07d4a541570a_at_mail.gmail.com>

Thanks, Maxim, that is very interesting. But why does Oracle even allow it? I assume the date ranges are typically enforced via some kind of internal constraint, and sqlldr bypasses constraints for the sake of speed. Is this really a "feature"? =)

On Sat, Jan 30, 2010 at 00:41, Maxim Demenko <mdemenko_at_gmail.com> wrote:

> It happens sometimes if application insert the date in the internal format
> without validation (there are some Notes on MOS about such logical data
> corruption, mostly in conjunction with sqlldr direct load). You can mimic
> such behaviour too:
>
> SQL> declare
> 2 l_century number;
> 3 l_year number;
> 4 l_month number;
> 5 l_day number;
> 6 l_hour number;
> 7 l_minute number;
> 8 l_second number;
> 9 l_date date;
> 10 l_date_hex raw(14);
> 11 begin
> 12 for i in 1..4 loop
> 13 -- this part is irrelevant - only provides test dates
> 14 l_century := -48;
> 15 l_year := -90;
> 16 l_month := 1;
> 17 l_day := 27;
> 18 l_hour := 11;
> 19 l_minute := 12;
> 20 l_second := 45;
> 21
> 22 if mod(i,2) = 0 then
> 23 l_century := -49;
> 24 l_hour := 14;
> 25 l_minute := 46;
> 26 l_second := 17;
> 27 end if;
> 28 -- end of irrelevant part
> 29 l_date_hex := hextoraw(
> 30 to_char(
> 31 (l_century + 100) * power(16,12) +
> 32 (l_year + 100) * power(16,10) +
> 33 l_month * power(16,8) +
> 34 l_day * power(16,6) +
> 35 (l_hour + 1) * power(16,4) +
> 36 (l_minute + 1) * power(16,2) +
> 37 (l_second + 1),
> 38 'fm'||lpad('X',7*2,'X')
> 39 )
> 40 )
> 41 ;
> 42 dbms_stats.convert_raw_value(l_date_hex, l_date);
> 43 insert into gorsdav values(
> 44 dbms_random.string('U',5),
> 45 l_date
> 46 );
> 47 end loop;
> 48
> 49 end;
> 50 /
>
> PL/SQL procedure successfully completed.
>
>
> SQL> desc oracle.gorsdav
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> GORSDAV_TABLE_NAME NOT NULL VARCHAR2(30)
>
> GORSDAV_ACTIVITY_DATE NOT NULL DATE
>
> SQL> select gorsdav_activity_date
> 2 from oracle.gorsdav where gorsdav_activity_date < '18-Nov-2009';
>
> GORSDAV_ACTIVITY_DATE
> -----------------------------
>
> 27-Jan-4890 11:12:45
> 27-Jan-4990 14:46:17
> 27-Jan-4890 11:12:45
> 27-Jan-4990 14:46:17
>
> SQL> select extract(year from GORSDAV_ACTIVITY_DATE)
> 2 from oracle.gorsdav where gorsdav_activity_date < '18-Nov-2009';
>
>
> EXTRACT(YEARFROMGORSDAV_ACTIVITY_DATE)
> --------------------------------------
> -4890
> -4990
> -4890
> -4990
>
> Best regards
>
> Maxim
>
>
>
> On 29.01.2010 20:07, Charles Schultz wrote:
>
> Oracle 10.2.0.4.2 on Solaris 10:
>
> SQL > desc oracle.gorsdav
> Name
> Null? Type
> -----------------------------------------------------------------------------
> -------- ----------------------------------------------------
> GORSDAV_TABLE_NAME
> NOT NULL VARCHAR2(30 CHAR)
> GORSDAV_ACTIVITY_DATE
> NOT NULL DATE
>
>
> SQL > select GORSDAV_ACTIVITY_DATE from oracle.gorsdav where
> GORSDAV_ACTIVITY_DATE < '18-Nov-2009';
>
> GORSDAV_ACTIVITY_DAT
> --------------------
> 27-Jan-4890 11:12:45
> 27-Jan-4990 14:46:17
> 27-Jan-4890 11:12:45
> 27-Jan-4990 14:46:17
>
>
> SQL > select extract(year from GORSDAV_ACTIVITY_DATE) from oracle.gorsdav
> where GORSDAV_ACTIVITY_DATE < '18-Nov-2009';
>
> EXTRACT(YEARFROMGORSDAV_ACTIVITY_DATE)
> --------------------------------------
> -4890
> -4990
> -4890
> -4990
>
>
> Oracle Documentation<http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#SQLRF00202>states that the ranges for the DATE datatype are "-4712
> to 9999 (excluding year 0)".
>
>
> Curious that the Julian date is even zeroed out:
> SQL > select to_char(GORSDAV_ACTIVITY_DATE,'J') from oracle.gorsdav where
> GORSDAV_ACTIVITY_DATE < '18-Nov-2009';
>
> TO_CHAR
> -------
> 0000000
> 0000000
> 0000000
> 0000000
>
> Having trouble making sense of the DUMP data:
> SQL > select dump(GORSDAV_ACTIVITY_DATE) from oracle.gorsdav where
> GORSDAV_ACTIVITY_DATE < '18-Nov-2009';
>
> DUMP(GORSDAV_ACTIVITY_DATE)
>
> --------------------------------------------------------------------------------------------------------------------------------------------
> Typ=12 Len=7: 51,110,1,27,12,13,46
> Typ=12 Len=7: 50,110,1,27,15,47,18
> Typ=12 Len=7: 51,110,1,27,12,13,46
> Typ=12 Len=7: 50,110,1,27,15,47,18
>
>
> So how did these "out-of-range dates" get in?
>
> --
> Charles Schultz
>
>
>

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Jan 30 2010 - 05:49:03 CST