Re: Year 2000 -Reply

["Bhowmik, Bhaskar" <Bhaskar.Bhowmik@STAPLES.COM>]

Jim,
You are in for a surprize again. Peoplesoft has changed the NLS_DATE_FORMAT
back to DD-MON-YYYY in 7.04 tools. We are following a practice of extracting

CCYY MM DD portions from dates after converting the date to YYYYMMDD format
using PS procedure Format-DateTime with 'DEFCMP'. This makes the program
immune to NLS_DATE_FORMAT changes in SETUPDB.SQC.

In v4.x NLS_DATE_FORMAT no longer rules the format of date columns in
Begin-Select.
You have to set the environment variable SQR_DB_DATE_FORMAT. if
SQR_DB_DATE_FORMAT
is not set, the dates are returned in the default 'DD-MON-YY' format and you
would
get a runtime warning (7501). But this warning can be ignored by setting the
environment variable "OutputTwoDigitYearWarningMsg      = TRUE | FALSE
(Default TRUE)"

If NLS_DATE_FORMAT is set to DD-MON-YYYY. You most insert/update a 4 digit
year. Please
see the results below:

UPDATE                          Date as updated in Oracle

16-MAR-98                               16-MAR-0098
16-MAR-00                               ORA-01841 Year must be
                                        between -4713 and +9999
16-MAR-1998                             16-MAR-1998

Another environment variable of interest is
UseY2kCenturyAlgorithm          = TRUE | FALSE (Default False)

I'm not sure what the effect of setting it to TRUE would have on
insert/updates of 2 digit
years or does it only effect the date functions. I would really appreciate
if someone can throw some light. Please correct me if I have mentioned
anything wrongly.

Regards,
Bhaskar



-----Original Message-----
From: Jim Hardesty [mailto:jhardest@LMBERRY.COM]
Sent: March 19, 1999 9:03 AM
To: Multiple recipients of list SQR-USERS
Subject: Year 2000 -Reply


>>> Victoria Lo <VGL123@AOL.COM> 03/18/99 12:39pm >>>
Hello everyone;
Has anyone had problem to insert date of "29-FEB-00" into PeopleSoft tables
on Oracle platform? Is there any resolution? Please let me know. Thank you.
Vicki
--------------------
There are several answers, depending on exactly how you are inserting (or
updating).  Are you inserting with the online application, sql, sqr, or
something
else?

What we found:

As far as I know, oracle.ini on the client is no longer an issue.  We are
using 32
bit sql*net 2.3.2.1.4 and it no longer requires a client side oracle.ini.

The database side is another issue.  What default NLS_DATE_FORMAT should
the database be?

The online application is not dependent on NLS_DATE_FORMAT.    The online
always puts a format around the dates when inserting.  You can see this with
a
sql trace.

SQR is dependent on NLS_DATE_FORMAT only in the fact that the first thing PS
does in the standard routines (setupdb, I think) is do an alter session to
yyyy-mm-dd.  PeopleSoft has worked pretty hard to make all of their SQR's
independent of NLS_DATE_FORMAT.  They have avoided doing substr on dates
etc except in the date handling sqcs.  But I think you will find that there
are still
lots of sqr which assume native dates are going to be yyyy-mm-dd.  Which is
good, because they are.

This was a big surpise to us in tools release 7.03 because we have many
custom SQR with hard coded substr positions in them.. We had worked pretty
hard to make all of our dates substr($date,8,4) when native was dd-mon-yyyy
and
we were pretty proud of ourselves.  Then they switched it to yyyy-mm-dd and
blew
us out of the water.  Not a big deal though.  What we try to do now is
ALWAYS
use the PeopleSoft delivered routines to play with dates.  Except I don't
think they
have a routine to switch months to and from MON anymore.  Kinda irritating
if you
need MON on an interface.

SQL*Plus is the next issue.  Again, in PeopleSoft delivered sql, you will
find that
every insert has a date format around it so it is not dependent on the
database
NLS_DATE_FORMAT.  Not an issue.   For custom sql, we decided to set all .sql
files up to first do an alter session and set the format to dd-mon-yyyy.
Don't ask
why dd-mon-yyyy instead of yyyy-mm-dd.  It is inconsistent, but too bad. But
at
least any sql files are not dependent on the database NLS_DATE_FORMAT.  It
mostly turns out that we really shouldn't have any .sql files anyway.
Everything
really should be in either sqr or query.  Everything in .sql is just from
laziness on
our part.

Near as we can tell, Datamover scripts are not an issue.

Now, the big question.  What to set the database to?

It turns out that both yy and rr are bad.  Sort of.

If your database is set to yy, try updating your birthdate to 2001.  Won't
work, will
it?  That is because in yy mode, Oracle always sticks the CURRENT century on
the front of dates during updates/inserts.  Kind of fun when the current
date is in
2000 and you can now only update/insert dates in 2000 you can no longer
update
your birthdate to 1901.  This is becuase you are updating to 03-jan-00 and
Oracle
puts a 20 in front of the date.

 Note:  This was a SERIOUS issue to one of our non-PeopleSoft completely
custom Oracle systems.  They had major re-writes to do since all of their
input
data has 2 digit years and suddenly, with yy and the system date set to
2000,
everything was being inserted in 2098.

Your DBA will say that rr takes care of that.  And it does, sort of.  If you
change
to rr then Oracle uses the date windowing to update/insert dates. Which
means
that regardless of the CURRENT date, if the database is set to rr, then if
you try
to update your birthdate to 1963, no problem.  24-aug-63 is 1963.  But you
can no
longer insert/update dates to 1940.  24-aug-40 is 2040.  No ifs, ands, or
buts.
Oh, you can insert hire dates all day long because they always fall into
late 1900
or early 2000.  In fact, virtually every date except birthdates fall into
this category.
But birthdates fall into early 1900 and you can't insert/update a date <1950
unless
you put a format around it.

But this is only an issue if you don't either explicity do an alter session
(sqr) or
put formats around your dates (PS online, PS sql).  In fact, it is pretty
much only
a problem in sql*plus, assuming you don't put formats around your dates.

So, in truth, it doesn't really matter what the NLS_DATE_FORMAT is.
PeopleSoft
works no matter what because they make no assumptions about the format.  The
only time it matters is for your own convience when using other ways to get
to the
database like sql*plus.

The benefit of changing from yy to rr is minimal.  rr will work for the vast
majority
of dates, except any dates you want to insert in early 1900 or late 2000,
which
really isn't many of them.  But birthdate is one of them, so what's the
point?

Our decision is to change NLS_DATE_FORMAT to yyyy-mm-dd. That way
everything will be consistent when we use outside query tools and we won't
have
to remember to put formats around dates in sql.  But we aren't in any real
hurry to
get to it.  After all, the application works, sqr works, all of our sql
works,
everything works and will continue to work into 2000.  We just won't be able
to be
lazy when we are in sql*plus.

One other issue:  We use Oracle SQL Worksheet from Oracle Enterprise
Manager v 1.2.2 as an ad-hoc query tool.  It has a couple of problems.  Even
if
you change the DATABASE default format to yyyy-mm-dd, SQL Worksheet
continues to treat dates as if the format were dd-mon-yy.  And if you do an
alter
session and set it to yyyy-mm-dd you find that it displays dates with a
width of 9.
Which isn't enough to display yyyy-mm-dd.  Irritating, but not a big deal.

That's our story. Comments?  Corrections?  More info?

jim
jhardest@lmberry.com
(937) 296-4867