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

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 30 Jan 2010 07:41:34 +0100
Message-ID: <4B63D49E.5050503_at_gmail.com>

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

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

Received on Sat Jan 30 2010 - 00:41:34 CST