Another Y2K issue

general discussion
1

At the suggestion of a fellow employee I ran a test on my PC to see if the
client software was Y2K compliant. We are running BusinessObjects 4.1.2
against Oracle 7.3.2 (and higher) databases. I ran the following query:

select to_char(to_date(‘01-apr-01’),‘MM/DD/YYYY’) from dual;

You should get 04/01/2001

I first ran it through a SQLPlus Window and it returned with a date of 1901.
I went out to my windows registry and modified the Oracle software to have a
NLS_DATE_FORMAT of ‘DD-MON-RR’. This corrected the problem with SQLPlus
window and I was able to get the date to read 2001. I also ran it directly
against the server and bypassed the client software altogether. (The NLS Date
Format had also been correctly set on the server.) Once again it returned
with a date of 2001.

However, when I run this query in free-hand SQL in BusinessObjects I still get
1901. Can anyone explain this? I looked at BusinessObjects June Newsletter,
but it didn’t give me a clear answer as to why I’m getting these results. I
also scanned the archives, but I was unable to find any information on how to
resolve this problem.

Thanks - Greg

Listserv Archives (BOB member since 2002-06-25)

2

Greg wrote:
I ran the following query:

select to_char(to_date(‘01-apr-01’),‘MM/DD/YYYY’) from dual;

You should get 04/01/2001

I first ran it through a SQLPlus Window and it returned with a date of
1901.
I went out to my windows registry and modified the Oracle software to have
a
NLS_DATE_FORMAT of ‘DD-MON-RR’. This corrected the problem with SQLPlus
window and I was able to get the date to read 2001. I also ran it
directly
against the server and bypassed the client software altogether. (The NLS
Date
Format had also been correctly set on the server.) Once again it returned
with a date of 2001.

Listserv Archives (BOB member since 2002-06-25)

3

Look in the Program Files\BusinessObjects\Oracle\OCIV7.SBO file. See what
the InitDateFormat setting is. I bet you find it is ‘DD-MON-YY’.

When BusinessObjects logs you in to Oracle, it issues an ALTER SESSION SET
NLS_DATE_FORMAT command using the InitDateFormat string. (It also issues an
ALTER SESSION SET NLS_NUMERIC_CHARACTERS command using the
InitNumericSeparator string, which should be listed right below
InitDateFormat.) This format is overriding your database’s default setting.

Erich Hurst
Compaq Computer Corporation

“It is so easy to break eggs without making omlettes.” – C.S. Lewis

Listserv Archives (BOB member since 2002-06-25)

4

From: W.Muellner[SMTP:w.muellner@DELPHI.AT]

GREG.GREEN@AIRBORNE.COM wrote:

At the suggestion of a fellow employee I ran a test on my PC to see if
the
client software was Y2K compliant. We are running BusinessObjects 4.1.2
against Oracle 7.3.2 (and higher) databases. I ran the following query:

select to_char(to_date(‘01-apr-01’),‘MM/DD/YYYY’) from dual;

You should get 04/01/2001

WRONG!
the to_date() function requires either the format string to be entered, or
it uses
the date format which is set in your middleware layer. This is the setting
of the
NLS_DATE_FORMAT parameter. This can be controlled in BusinessObjecst via a
parameter in the ociv7.sbo file (I guess).
For ORACLE middleware it is set in the registry.

Hope this helps.

PS: You could have tried: to_char(to_date(‘01-apr-01’,
‘DD-MON-YY’),‘MM/DD/YYYY’).

this should give correct results. (I didn’t test it)

This of course should NOT, but to_char(to_date(‘01-apr-01’,
‘DD-MON-RR’),‘MM/DD/YYYY’) should :slight_smile:

Ryszard Mikke

–==> Hiroshima’45 Tschernobyl’86 Windows’95 <==–
R.Mikke@pl.vwfsag.de

Listserv Archives (BOB member since 2002-06-25)