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@xxxxxxxxx> 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