Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Oracle cannot find date for 1800's year

Oracle cannot find date for 1800's year

From: <suisum_at_freenet.edmonton.ab.ca>
Date: 1998/03/07
Message-ID: <6dq7k9$k76$1@news.sas.ab.ca>#1/1

I know this is stupid but I have to ask the date problem. I am dealing with a date field which value frange from 1800 to now. I do the following code to change the date string 'YYYYMMDD' into a date:

v_priority_date := TO_DATE(substr(priority, 1, 8), 'YYYYMMDD');
v_priority_date_text := TO_CHAR(v_priority_date, 'DD-MON-YYYY');
v_priority_date := TO_DATE(v_priority_date_text, 'DD-MON-YYYY');
v_priority_date_text := TO_CHAR(v_priority_date, 'DD-MON-YY');
v_priority_date := TO_DATE(v_priority_date_text, 'DD-MON-YY');

Then I use the follwing select statement to search for the record:

        SELECT water_allocation_id
          INTO v_water_allocation_id
          FROM water_alloc_priorities
         WHERE priority_number_date = v_priority_date
           AND priority_number = v_priority_number;

It comes to my notice that some of records in the range 1889 - 1899 cannot be pciked up the query. What's wrong?

I tried to use the compare statement like:

   WHERE TO_CHAR( priority_number_date, 'YYYYMMDD') = TO_CHAR( v_priority_date, 'YYYYMMDD')

But it took 9 hours to run the job as Oracle will fo a full table scan for each record is selected.

Please help.

--
Best regards,
Received on Sat Mar 07 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US