ORA-not a valid Month error

building semantic layer
1

Hi All,
I have Oracle DB, trying to create a filter in IDT, on a Date field with Data as:
Exp Date

Jan 9, 2013
Jun 11, 2014
May 12, 2012

My Prompt filter will give me a start Date and a End Date for Date Range to select Exp Date.
I use the Exp Date field for the List Of Value and made 2 parameters, Start Date and End Date using this list of value.
However when I try to run this in webi, the Prompt shows me Date in the following format :

Jan 9, 2013 12:00:00 AM

In other words it is coming as Timestamp. If I select the Dates then I get an Oracle error:

ORA-01843 Not a valid Month

Need help to solve this. Can I use another way to select Date Range?
:hb: :hb:

americanmc :hong_kong: (BOB member since 2009-12-31)

2

Run your sql code in TOAD or SQL Developer. Check your date column, use trunc if need be.

NycPriya (BOB member since 2010-11-04)

3

Try to give the date as 9-Jan-2013 only and remove the time. then it will work

surya_sudheer :india: (BOB member since 2006-11-01)

4

Don’t know if this will work.

TRUNC(TO_DATE(table.datecolumn,"MM/DD/YYYY") )= TO_DATE(@Prompt syntax,"MM/DD/YYYY")

[Moderator Edit: Added code formatting - Andreas]

NycPriya (BOB member since 2010-11-04)

5

Hi All,
I have re started work on this issue, I tried the solutions offered but nothing worked so far.
The thing I have noticed is that my Exp Date object when I drag it to the filter panel and let’s say try to select a Date , e.g. 7/20/2014
When I view the SQL syntax in Query panel it shows me

EXPDATE   IN  ( '20-07-2014 00:00:00'  )

So you can see the Date is shown first and that is why the error coming up “Not a valid Month” since 20 is not a valid month.
Here is my new syntax for Exp Date :

to_date(to_char(TABLE.EXPDATE,'MM-dd-yyyy'),'MM-dd-yyyy')

Although I have defined Date as MM-dd-yyyy but somehow in IDT and in WEbi it is shown as dd-mm-yyyy.

It is Oracle Db. Looking for helpful tips, :crazy_face: :crazy_face:
thanks

americanmc :hong_kong: (BOB member since 2009-12-31)

6

Webi is the culprit, because it gives the date as a string without a clean TO_CHAR.
The date format that you define in Designer has nothing to do with it, this is only an output formatting for tables.

I don’t know if the following really works:

 alter session set nls_date_format = 'MM-dd-yyyy' nls_territory = 'AMERICA' n

Hope it helps.

KFonMurphi :fr: (BOB member since 2007-10-16)

7

So far I tried both your ideas but nothing is working. I have now resorted to making my Date field as a String and only then it works, otherwise it always changes the Date format.
This issue is mind boggling. :cuss: :cuss:

americanmc :hong_kong: (BOB member since 2009-12-31)

8

Hi

Are you using the calendar drop down select to get your dates? If you are then you can modify the code in that to give you the correct date format back

David

nwdb :uk: (BOB member since 2005-10-26)

9

The PRM file is usually the one to change to solve this. You need to restrty the connection server once you have changed the prm file for the Oracle database.

If that still does not work, then there is this check list to go through. The PRM file change is also detailed there:-

https://bobj-board.org/t/84923

Mak 1 :uk: (BOB member since 2005-01-06)

10
  1. This happens when you see
    where the Month is normally written before the day

but the format in the application is still the “European” style.
Where the day is written before the month.

  1. Write numeric month in Month mask instead of “Jan” :+1:

ravikant (BOB member since 2015-07-10)

11

Welcome to B :mrgreen: B

As I said already, the prompt not matching, in the terms of format, is usually the Input date parameter in the PRM file. Also, he is using a numeric in his month mask, already.

Mak 1 :uk: (BOB member since 2005-01-06)