Since there is nothing totally clear on Oracle,BO, and Dates I offer this. Please tell
me if I am wrong.
To_Date is an sql function that converts a character string into a date that
is stored in Oracles internal date format.
Input into the to_date function can be a string literal or database
column of varchar2 and chart datatype.
Regardless of the date format used, Oracle stores the date in one
standard format.
The default display date format in Oracle is DD-MON-YY. This
can be changed with the following command oracle command.
Alter session set nls_date format = DD-MON-YY.
For a BO client to data provider to return dd-MON-yy by default.
You must change the date format to dd-mon-yy in the XP client nls
settings on all clients to get the query to return this format and to take
Input dates in this format. Plus Change the following in the sbo.
Since no one is going to every client and change the nls settings
the following would be the standard approach to handle dates
formats in bo using Oracle.
1.Change the object format in the universe so that bo displays the date in dd-mon-yy.
2.To accept dd-mon-yy in a bo prompt do this.
trunc(to_date(@Prompt(‘As of Date (DD-MON-YYYY)’ ,‘A’,MONO,FREE),‘DD-MON-YY’))
I did a trace on what BusinessObjects sends to Oracle for a report and I noticed the BO appears to send the following before it actually sends the SQL query
ALTER SESSION SET NLS_DATE_FORMAT ='DD-MM-YYYY HH24:MI:SS'
Overall, I don’t think it matters - what gets returned is a date and BusinessObjects then formats it how it pleases. The date format in Oracle is totally independent of the date format on a report.
First, I would think there is some kind of personalization somewhere for the format of the inputted date because those wacky Frenchman enter their dates DD/MM/YY. Second, you don’t need to do the trunc as the converted date will automatically have no hours portion.
Are computing environment is Oracle Applications which uses dd-mon-yy. So the user wants to see only dd-mon-yy. However, since we are a zabo shop you would need to go to every client and change the nls to have the date defualt to dd-mon-yy. Or use to_date and format object to provide dd-mon-yy format. This thread should be particular interest to any Oracle Apps shop since Oracle Apps displays dd-mon-yy.
Note: I believe for zabo the sbo file changes get made on the server. But you would still need to change the nls on every machine to defualt to dd-mon-yy.
I have been working with Oracle Applications for years and my first thought is, “Don’t do it”. As long as you are consistent, the users can easily adjust. Oracle Applications has the non-standard date format and people can easily adjust to using the standard one. Which would you rather train the users on, to enter the date in BusinessObjects just like they have written it all their life, or that the SQL error “ORA-01858: a non-numeric character was found where a numeric was expected :-1858” means that you typed the date in wrong.
On the other hand, I would never create a condition for Oracle Applications that takes a straight date. Oracle Applications users are used to being able to skip over prompt values and to mimic that, I allow the users to enter a “%” in all prompt values to effectively skip it.
We are an Oracle shop and we never use that format. I am pretty sure we just changed the default date format in Oracle. We use the normal mm/dd/yyyy format in our queries and prompts.
(BOB member since 2003-10-22)
(BOB member since 2003-09-17)
(BOB member since 2002-06-20)