Re: Y2K date problem - anyone help?

I've found that I also need to add this line to my LOGIN.SQL (on my client, in \ORANT\DBS\LOGIN.SQL)

alter session set nls_date_format = 'DD-MON-YYYY'; or
alter session set nls_date_format = 'DD-MON-RR';

Suresh Bhat <suresh.bhat_at_mitchell-energy.com> wrote in message news:01bf2b0d$4b4a6840$a504fa80_at_mndnet...
> 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 Wed Nov 10 1999 - 15:42:11 CST