Re: Y2K date problem - anyone help?

Dave,

The easiest way do this would be to set nls_date_format in initXXXX.ora file to dd-MON-rr, shutdown and restart the database. This will correctly interprete '02-jan-00' as January 2, 2000.

With dd-MON-yy that date is interpreted as January 2, 1900.

Briefly, rr(RR) format interpretes dates as being between 1950 and 2049, where as yy format interprets dates as being in the current century.

Hardest way to do this would be in every application that is not Y2K compliant:

select * from delivery where
delivery_due_date between
 to_date(&P_from_date, 'dd-mon-rr') and to_date(&P_to_date,'dd-mon-rr');

Good Luck !!!

Suresh Bhat
Oracleguru
www.oracleguru.net

Dave <daveg_at_tcp.co.uk> wrote in article <382889de.430615_at_news.tcp.co.uk>...
> Hi everyone,
>
> I have a little problemette. We are currently using Oracle 7.1.3 and
> all our date fields are held in the format dd-mon-yy.
>
> I have recently found some sql that isn't y2k friendly, here goes:
>
> SELECT * FROM delivery WHERE delivery_due_date > &P_from_date
> AND delivery_due_date < &P_to_date
>
> Okay, if '02-dec-99' is entered for parameter &P_from_date and
> '31-dec-99' for &P_to_date then I get the results I expect, ie, all
> deliveries due between the two dates. However, if I use '02-dec-99'
> and '02-jan-00' then I get no rows selected when in reality I should
> get some.
>
> Is there some way I can get this to work using to_char maybe?
>
> Any help is appreciated.
>
> Dave
>
Received on Tue Nov 09 1999 - 15:57:01 CST