| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle cannot find date for 1800's year
Not knowing your data structure I may be missing something, but ...
v_priority_date := TO_DATE(substr(priority, 1, 8), 'YYYYMMDD');
Is the only thing you need to do, you have a date in Julian format which will compare directly and correctly. No need for the next five statements. You run into trouble when you do
v_priority_date_text := TO_CHAR(v_priority_date, 'DD-MON-YY');
because you lose your century digits.
Ben Salemi
Global Consulting Group
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');
>
> 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,
>
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Mar 09 1998 - 00:00:00 CST
 |
 |