How does one enter dates beyond the implicit, Oracle internal date limits?
- From: Charles Schultz <sacrophyte@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 29 Jan 2010 13:07:05 -0600
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:
