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

  • From: Maxim Demenko <mdemenko@xxxxxxxxx>
  • To: sacrophyte@xxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Sat, 30 Jan 2010 07:41:34 +0100

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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 01-2010