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: Sybrand Bakker <postmaster_at_sybrandb.demon.nl>
Date: Fri, 4 Feb 2000 22:59:43 +0100
Message-ID: <949701714.27611.0.pluto.d4ee154e@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 Fri Feb 04 2000 - 15:59:43 CST

Original text of this message

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