Oracle format mask generated by Webi document

[cmmrfrds]

I am using a date to filter with in a Webi document that goes against an Oracle 11G database from BO XI R2. The format that is generated is like so.
IMCUST.V_ENCOUNTERS.CONTACT_DATE In ( '02-02-2010 00:00:00' )

How do I set in the Universe that either the default Oracle date literal is generated i.e. DD-MON-YYYY or it puts a format mask on the date literal like so.
to_date( '02-02-2010 00:00:00','DD-MM-YYYY HH24:MI:SS' )

Thank you.

 

[hilfy]

In a previous job, I did a lot of work with universes and WebI on Oracle and never had any problems working with dates in filters.

In your universe, is the Ansi92 parameter set to Yes? If so, try setting it to No.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[cmmrfrds]

Hilfy, why would the ANSI92 parameter affect the date mask sent to Oracle?

Right now on our system the date literal from a Universe/Webi is generated as a literal string like so.
IMCUST.V_ENCOUNTERS.CONTACT_DATE In ( '02-02-2010 00:00:00' )

In Crystal the date literal looks like so.
TO_DATE ('02-02-2010 00:00:00', 'DD-MM-YYYY HH24:MI:SS')

Notice there is a format mask on the date literal. I have a dimension table joined to a view that I treat as a fact table. I would like to select a date from the dimension table which will be generated in the query as a literal which I would like to see converted to a date literal with a mask instead. Oracle will push a literal down into the View, but does not seem to do that if the date is not formatted. For example, if the underlying table has 40 million records I don't want the View to do a table scan on the 40 million when it can use the date index to retrieve 40 thousand instead.

 

[hilfy]

ANSI 92 can change the way queries are built, including things like how date format strings are set. That's why I asked that particular question.

Unfortunately, I don't currently have access to a system that runs against Oracle, so I can't go back and check to see for sure what was happening with that parameter set to No.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.