Re: Oracle cannot find date for 1800's year

From: Steve Cosner <stevec_at_zimmer.csufresno.edu>
Date: 1998/03/07
Message-ID: <6ds3ti$iqp@info.csufresno.edu>#1/1

In article <6dq7k9$k76$1_at_news.sas.ab.ca>,  <suisum_at_freenet.edmonton.ab.ca> wrote:
>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');

In the above code, you should only use the first TO_DATE conversion. The second is unnecessary, and the third is causing your problem.

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.

>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.
  Received on Sat Mar 07 1998 - 00:00:00 CST