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

Maxim already answered the main question. I want to point out that your nls_date_format hides the negative sign in your query:

SQL > select GORSDAV_ACTIVITY_DATE from oracle.gorsdav where GORSDAV_ACTIVITY_DATE < '18-Nov-2009';

GORSDAV_ACTIVITY_DAT

You need to set year part to syyyy instead of yyyy.

You can also use dbms_stats.convert_raw_value to confirm the dump()'ed date value. Your first row is

Typ=12 Len=7: 51,110,1,27,12,13,46

which is 336E011B0C0D2E concatenated in hex. We can confirm it's this date value:

SQL> set serverout on
SQL> alter session set nls_date_format = 'dd-mm-syyyy hh24:mi:ss';

Session altered.

SQL> declare d date;
  2 begin
  3 dbms_stats.convert_raw_value('336E011B0C0D2E', d);   4 dbms_output.put_line(d);
  5 end;
  6 /
27-01--4890 11:12:45 <-- note the negative sign for year

PL/SQL procedure successfully completed.

Yong Huang       

--

http://www.freelists.org/webpage/oracle-l Received on Sat Jan 30 2010 - 12:07:17 CST