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

From: Yong Huang <>
Date: Sat, 30 Jan 2010 10:07:17 -0800 (PST)
Message-ID: <>


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';


27-Jan-4890 11:12:45

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       

-- Received on Sat Jan 30 2010 - 12:07:17 CST

Original text of this message