Help - there must be a gremlin in my system ! Symptoms: a report retrieves information by date (journal effective date in my Oracle Financials database), for a date range parameter. Some ranges produce correct data, others do not. The only way to make the report produce correct data for every selection is to go to Start/Settings/Control Panel/Regional Settings and “toggle” the short date date style setting, i.e. change it to any other format. Well, at least that’s the only way we’ve managed to come up with until now. It does not seem to matter what the new date format is set to, as long as the setting has been activated. To make things even more interesting, the described aberration happens only on some of the PCs running this report, and not on others.
Any help ? Is there something very obvious that I’m missing ? Any other victims of technology ? I would be grateful for any suggestions, as at the moment we are at a standstill as far as finding a proper solution. Thanks in advance. Cheers, Irena
Hi Irena,
You say sometimes the date ranges do not retrieve correct results. Are you including a time with your dates? If you use a range from say ‘between July 7 and July 10’, it actually sets the time value to 00:00:00. This means the inclusion is only to July 10th at 00:00:00 which is the first minute of July 10th. If you want to include all of July 10th, you should specify the time as e.g., 23:59:59.
Sincerely,
Roger Poole
rpoole@nswc.navy.mil
Symptoms: a report retrieves information by date (journal effective date in my Oracle Financials database), for a date range parameter. Some ranges produce correct data, others do not. . . . Irena
Has anybody encountered either of the following two problems ?
Problem 1 (Webi v2.02 & Full Client v4.1.5) ---------
When I use dates in a condition BEFORE 7/2/3000 (trail and error gave this date), the
default time set up in the condition is 00:00:00 When I use dates in a condition GREATER or EQUAL to 7/2/3000 (d/m/yyyy) Business Objects
seems to go crazy
Inserts a random time in the Condition panel, but use the SQL button and there’s a different random time !
This question has probably been answered a million times but being new to Business Objects and having no training yet on the product I am struggling with something. WE hit an Oracle DB which stores dates as mm/dd/yy hh:mm:ss. I need to get that to a mm/dd/yy format for display and querying. I first used the to_char function but that does not work correctly. I have been told to use the trunc function but (1) I do not know the syntax for it and (2) I was told using the trunc function will force an entire table scan every time it is used. Can someone help please it is greatly appreciated. Thanks
Rick
For a quick hit I would say just use the format option within reporter. I.e. if your date field is being reported as dd/mm/yy hh:mm:ss as standard simply right click on the object in the report select Format Cell, then Date/Time and choose a suitable format. This means you don’t have to fiddle about in designer. The trunc format would have the syntax trunc(yourobject). Another thing, in designer when you create your date object you can select object properties and force it to always pick a particular format e.g. mm/dd/yy
Nick
********************************************************************** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager.
This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses.
Why not just trying to use the object as a date, and define the format as “mm/dd/yy”. The format can be defined by default in designer (right-click on the object and define the format) or in the report by defining the format of the cell?
However, if you are querying for dates equal, say, to “06/29/00”, only those with 0 hours, 0 minutes and 0 secods will be returned. You might not be able to do without the TRUNC ( the_date_field , ‘DD’) .
For display, the format described by chris is all that is needed. For query, you do have to avoid the trunc command because of the full table scan (if the column is indexed, otherwise, it’s a full table scan anyway.)
A condition object using the following SQL will filter for a prompted date range w/o a full table scan (if the date column is indexed.)
table.column >= TO_DATE(@Prompt(‘Enter beginning date MM-DD-YYYY.’,A,),‘MM-DD-YYYY’)
AND table.column < TO_DATE(@Prompt(‘Enter ending date MM-DD-YYYY.’,A,),‘MM-DD-YYYY’) + 1
If you change the prompt text to be exactly the same for both @prompts, this would display as a single prompt and retreive all rows for the single date value entered at the prompt.
Tony Comer
__________________________________________________ Do You Yahoo!?
Get Yahoo! Mail - Free email you can access from anywhere! http://mail.yahoo.com/