Oracle Date error in IDT :Not a Valid Month

building semantic layer
1

Hi All,
I am facing issue where I have a DateTime field “Created Date” in Oracle DB. In the universe,the Date field returns Data fine.But if I try to use it as a filter , for example using the Queries area in IDT, I put it as

CreateDate Inlist 'Jun 22 2015'

It gives error saying “Not a valid Month”. When I look at the query SQL, it shows

CREATEDDATE  =  '22-06-2015 14:39:49'

Here I discover it is appearing in the wrong format, it is reading 22 as the month hence the error.

I tried something in the Advanced Tab in the Date field and put in the blank bar under Database Format {!d ‘yyyy-mm-dd’}. However still getting error, while I know that this format is working for me on other Date fields.
Is there a workaround here to fix this, not sure why Oracle is reading it in a different format and giving this error.
Need your input, Thanks…
:crazy_face: :crazy_face:

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

2

Shouldn’t the date format read dd-mm-yyyy rather than yyyy-mm-dd?

Either that or take a look at the regional settings within your Windows control panel; it can be fixed from there at times.

3

The British Date is “dd-MM-yyyy” but the American Date is “MM-dd-yyyy”.
I tried to set the regional Date format in Windows control panel to no avail.
I belive something needs to be changed in the oracle parameters since it is reflecting date in British format while we need it to show in US.

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

4

I’ve never got Oracle datetimes to work correctly. As you say, they will parse and work in a SELECT, but as soon as you use them in a filter, they error.

I always convert to CHAR and then back to DATE, which works perfectly. Doesn’t seem to impact processing time at all.

Debbie

Debbie :uk: (BOB member since 2005-03-01)

5

Thanks, I tried to_char , it shows a strange syntax error, which is missing quotaion marks as below:

to_char(CREATEDDATE, 'MM-dd-YYYY HH24:MI:SS')=  07-23-2012 07:29:00

Then if I manually edit sql and add the quotation marks :

to_char(CREATEDDATE, 'MM-dd-YYYY HH24:MI:SS')=  '07-23-2012 07:29:00'

It works fine. Of course I cannot manually change this during webi query execution so it is still an issue.
Not sure how to resolve this, if I use to_date to mask this, gives different syntax errors, such as literal does not match , not a valid month etc…

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