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

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Fri, 29 Jan 2010 13:07:05 -0600
Message-ID: <7b8774111001291107g852fdd8tb5012965f4e94784_at_mail.gmail.com>

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 Fri Jan 29 2010 - 13:07:05 CST