Date problems revisited

building reports desktopintelligence
1

Hello, everyone,

We’ve had a problem with reports using date parameters for some time now - and no solution from the local BO support. In short, a user selects DateFrom and DateTo, runs the report, and gets wrong results (dates in the report are not those selected in the parameter box). The only workaround we were able to come up with requires the user to go to the Control Panel/Regional Settings/ Date/Short date style and to “toggle” the date format. And I mean, literally, toggle - it does not seem to matter what format is selected, as long as the format field has been in any way changed. From now on, for the duration of the session, any reports using date parameters will work correctly.
I have checked the ociv&.sbo file, and the InitDate and InputDate formats are NOT the same.
Input = “DD-MM-YYYY HH:MM:SS”;
Init = “DD-MM-YYYY HH24:MI:SS” These settings were provided by
a BO consultant and seem to be the only ones that work (well, they allow the reports to execute !).
I’ve re-run the report with some changes in the formats, with the following results:

  1. HH:MM:SS
    HH24:MM:SS or

HH:MM:SS
HH:MM:SS or

HH24:MM:SS
HH24:MM:SS produce ORA-01843 error: Not a valid month.

  1. HH24:MI:SS
    HH24:MI:SS produces ORA-01830 error: date format picture ends
    before
    converting entire input string.

Now, does anybody have any pointers or suggestions (well, a working solution would
be a blessing …) ? Thanks in advance, cheers, Irena

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

2

On Wed, 30 Sep 1998 12:04:36 Irena.Sosinska wrote:

Hello, everyone,

We’ve had a problem with reports using date parameters for some time now - and no solution from the local BO support. In short, a user selects DateFrom and DateTo, runs the report, and gets wrong results (dates in the report are not those selected in the parameter box). The only workaround we were able to come up with requires the user to go to the Control Panel/Regional Settings/Date/Short date style and to “toggle” the date format. And I mean, literally, toggle - it does not seem to matter what format is selected, as long as the format field has been in any way changed. From now on, for the duration of the session, any reports using date parameters will work correctly.
I have checked the ociv&.sbo file, and the InitDate and InputDate formats are NOT the same.
Input = “DD-MM-YYYY HH:MM:SS”;
Init = “DD-MM-YYYY HH24:MI:SS”
These settings were provided by a BO consultant and seem to be the only ones that work (well, they allow the reports to execute !).
I’ve re-run the report with some changes in the formats, with the following results:

  1. HH:MM:SS
    HH24:MM:SS or

HH:MM:SS
HH:MM:SS or

HH24:MM:SS
HH24:MM:SS
produce ORA-01843 error: Not a valid month.

MM is the Oracle definition for the numeric version of the month. Using today’s date as an example, what you are trying to do is 12:09:30. Where 09 is September. MI is used to represent minutes.

  1. HH24:MI:SS
    HH24:MI:SS
    produces ORA-01830 error: date format picture ends before converting entire input string.

It should since it’s expecting any date passed into the variable or field to be in Oracle’s date format, which has been defined to be DD-MM-YYYY HH:MM:SS or DD-MM-YYYY HH24:MM:SS. Since you are only attempting to pass in a time, Oracle has no idea what date the time is refering to. A TO_DATE function would have to be used to convert the time to a “valid” date. Valid only from an Oracle viewpoint. But the actual date Oracle would use would be, 09-01-1998 12:18:30. Defaulting to the first day of the current month/year. Which is not what you are wanting I believe.

Now, does anybody have any pointers or suggestions (well, a working solution would be a blessing …) ? Thanks in advance, cheers,
Irena

As far as why you have to toggle the date parameters in Windows to get the report to work is a mystery to me but I am sure that some one will have an answer for you shortly.

Hope this helps explains why you were getting those errors.

Jim Droppa
Source Consulting

Free web-based email, Forever, From anywhere! http://www.mailexcite.com

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