Re: Year 2000 -Reply

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

Kathy,
You have to set SQR_DB_DATE_FORMAT in SQR.INI

[Environment:Oracle]
SQR_DB_DATE_FORMAT=DD-MON-YYYY

Thanks,
Bhaskar

-----Original Message-----
From: Kathy Mason [mailto:kmason@GIX-GLOBAL.COM]
Sent: March 19, 1999 10:32 AM
To: Multiple recipients of list SQR-USERS
Subject: Re: Year 2000 -Reply


I think this is my problem.  Where do I set the SQR_DB_DATE_FORMAT?

thanks,
kathy

"Bhowmik, Bhaskar" wrote:

> 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