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

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

I am confused in this issue.

 

[BeeBear]

Hi There,
I can't give you the whole answer, but it seems similar to other issues I have had in relation to LEAP YEAR dates.
I bet if you try this report again now it will work since its no longer Feb 29th!

HTH a bit!

regards

BB

*** Count your blessings not your problems******

 

[jdemmi]

Yep, OR -- there is an invalid "date" in the dataset somewhere. (in the field that you're converting to a date.

-- Jason
"It's Just Ones and Zeros

 

[lbass]

I think this is really an Oracle date formatting issue. Try changing the format to:

to_date({field}, 'YYYY-MM-DD')

If this still doesn't work, you might want to post in an Oracle forum.

-LB

 

[jdemmi]

that syntax should work fine in Oracle, depending on the date string.

for example to_date(field, 'yyyy/mm/dd')

would work if the values in "field" were formatted like 20080301

herein lies the problem. the minute someone stores this string like, 200831 the to_date will error out if the offending date is in the range of your query

-- Jason
"It's Just Ones and Zeros

 

[devnaren]

still it is giving me an error as not a valid month. Just I want to know that why it works fine before 29th of Feb and why it is not working now.

 

[CoSpringsGuy]

are you certain there is always data in the raw database field? If it tries to evaluate an empty or null value that error might be recieved. If that is the case you would want to evaluate for those instances in your universe.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[devnaren]

Can you help me out how to do it in the Universe.

 

[devnaren]

I re-created this report with out using Universe and BO XI server. Its giving me the exact result what I need.

when i am trying to save this report on the server its giving me an error as failed to read the parameter object.

I think its not matching the date format in BO XI server.

How to set the date format in BO XI Server as 'MM/DD/YYYY'