0

Looking into a query in WebIntelligence, after running, the prompts are replaced by values provided by user (for instance dates). When I run the same query on Oracle (because this database I use for my universe) I’m getting error in terms of dates. Dates in query (in BO) are just strings, like StartDate = '30-06-2020 00:00:00′. When I run the query generated in WebIntelligence on Oracle I’m getting error: ORA-01843: not a valid month 01843. 00000 – ” not a valid month” And to fix this I need to use for instance to_date function and then it’s working fine. My question is: how dates are parsed in WebIntelligence while running a query? so the mentioned error does not occur?

dropx
  • 25
  • 8
  • What is the datatype of **StartDate** in your universe? Does that SQL script that WebI generates run for you on Oracle? – Isaac Aug 13 '20 at 22:08
  • @Isaac it's 'Date' type. This script does not work for me on Oracle but when I add to_date function (with mask of course) it works. So I'm wondering how it's working when BO pushes the script towards Oracle with StartDate equal to string like: `StartDate = '30-06-2020 00:00:00'` – dropx Aug 13 '20 at 23:35
  • I will take a look a few things tomorrow. – Isaac Aug 13 '20 at 23:37

1 Answers1

1

I am getting the same error as you when I try a query directly against Oracle using SQL Developer that works in Web Intelligence. According to this BusinessObjects makes a call to set the date format.

So you can do that either in the preferences of SQL Developer (or presumably whatever database query tool you are using) or explicitly setting it with the alter session command.

alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';

select...[the rest of your query]

Both options are shown in the answer to How can I set a custom date time format in Oracle SQL Developer?.

Isaac
  • 3,240
  • 2
  • 24
  • 31