Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Oracle cannot find date for 1800's year
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