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.
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.
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.
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