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

  • From: Yong Huang <yong321@xxxxxxxxx>
  • To: sacrophyte@xxxxxxxxx
  • Date: Sat, 30 Jan 2010 10:07:17 -0800 (PST)

Charles,

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


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


Other related posts: