Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: sysdate assumes year 1900 in months_Between calculations

Re: sysdate assumes year 1900 in months_Between calculations

From: Martin Vonk <mwvonk_at_wxs.nl>
Date: Sat, 5 Feb 2000 11:51:01 +0100
Message-ID: <87gvbc$3d71f$1@reader2.wxs.nl>


Hi Sybrand,

Thanks for the reply

Martin

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> schreef in berichtnieuws 949701714.27611.0.pluto.d4ee154e_at_news.demon.nl...
> Lesson: set your registry key nls_date_format to dd-mon-rr
> and you won't have this problem upon insert, or make sure you set
> nls_date_format in your script
> or better still:
> always use to_date() and never make any assumptions.
> I have a feeling a bunch of people are going to be a victim of this
insert
> script.
> Shouldn't we submit a TAR? Thomas? Pete?
>
> Regards,
>
>
> --
> Sybrand Bakker, Oracle DBA
> Martin Vonk <mwvonk_at_wxs.nl> wrote in message
> news:87f8de$2n3mi$1_at_reader3.wxs.nl...
> > Hi,
> >
> > I've found the solution for my problem and maybe yours.
> > I've installed PO804 in 2000, so for the computer it is 2000. Tables
like
> > Emp are installed automatically, but the format of "Hiredate" is
> > 'dd-mon-yy'. So, Oracle recognizes it as 2080.
> > Lesson, be aware of insert scripts
> > Mightbe the answer for your "probem as well"
> >
> > Joe Worthington <josephwor_at_hotmail.com> schreef in berichtnieuws
> > 87es7q$k1v$1_at_nntp9.atl.mindspring.net...
> > > I am comparing sysdate (now 2000) to a date field. Oracle is assuming
> that
> > > sysdate is 1900. See code below.
> > >
> > > select
> > > ketsTimeAcctSalesman,
> > > sysdate System_Date,
> > > ROUND(MONTHS_BETWEEN(Sysdate, ketsTimeAcctSalesman),0)
> > > Time_Assigned_to_Salesman
> > > FROM KETS_ACCT_PROFILE
> > > where (ROUND(MONTHS_BETWEEN(Sysdate, ketsTimeAcctSalesman),0) < 0);
> > >
> > > This code produces the following.
> > >
> > > KETSTIMEACCTSALESMAN SYSTEM_DATE TIME_ASSIG
> > > ------------------------------ -------------------- ----------
> > > 15-SEP-99 04-FEB-00 -1195
> > > 05-OCT-99 04-FEB-00 -1196
> > > 03-FEB-99 04-FEB-00 -1188
> > > 01-FEB-96 04-FEB-00 -1152
> > > 02-APR-98 04-FEB-00 -1178
> > > 01-JAN-99 04-FEB-00 -1187
> > > 01-JAN-99 04-FEB-00 -1187
> > >
> > > Any Ideas ?
> > >
> > >
> > >
> > >
> >
> >
> >
> >
>
>
Received on Sat Feb 05 2000 - 04:51:01 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US