Very Computer

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,

When you do a TO_DATE and only include a 2-digit year, the system
assumes the century of the client or server where the commands are
running.  Since this is 1998, all your years get 19 for the century.
And guess what happens in 2000?

Steve Cosner
----------------------------------------------------------
http://members.aol.com/stevec5088
Downloadable utility form -- Display and update any table.

Quote:>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.