ORA - 01843 Error: Not a valid month

[devnaren]

Hi All,

We are using Crystal Reports XI to create reports and executing those reports from the Business Objects XI R2 and using Oracle 9i database.
I created a report, in that i am using an object SubmittedDate which is coming from universe. In the database the field datatype is varchar2(255). So I created object in universe as to_date({field}, 'mm/dd/yyyy').

On the report side, i created two parameters as StartDate and EndDate which is set to Date format.

Now when i am trying to write a Record Selection formula as

{SubmittedDate} >= {StartDate} and
{SubmittedDate} <= {EndDate}.

its giving me an error as

ORA-01843: Not a Valid Month.

Still yesterday it was working fine. But I don't know why it is not working from 29th of Feb.

when i check it in search engine about this issue, its says like NLS_DATE_FORMAT.

I am confused in this issue.

 

[gonzo27]

This site will be a bit more specific on the error messages you get..

http://ora-03277.ora-code.com/all.html

 

[Tarek78]

Hi,

I had the same problem. If you want to change the date format in BO you can see theses files :
- oracle.prm in Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\oracle for oracle DB.
- sqlsrv.prm in Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86\dataAccess\connectionServer\odbc for SQL server DB.

See also the generated sql query of the report. If your SELECT statement contains @variable which is DATE type, you need customize your query by adding a TO_DATE(@variable,'your format').

regards,
Tarek