I read this thread about using trunc() on date objects, but I am not sure if this is just a BusinessObjects issue or something I can fix in the Designer.
I have an Oracle 9i DB, and a table with a DATE column. An object in the universe is defined as type Date and the definition is just that date column’s name. That column in the table is (unsuprisingly) filled with regular dates, like 7/8/2005. Using the object in a report shows 7/8/2005. But when I go to Data->View Data, that object shows up like 7/8/2005 12:00:00AM. This in turn causes the 3349 Numeric error when exporting to Excel.
I tried changing the definition of the object to trunc(date_column), and nothing is affected. Has anyone else noticed this? It seems BusObj is just converting the object to DateTime internally irregardless of what it actually is.
I am not sure why I get the error either, but it doesn’t seem totally uncommon. I’m only trying to export 3 columns and about 50 rows, so I don’t think the problem is the 65k limit. All my dates are also in 2005.
Cindi,
That is interesting, I will try that out and let everyone know how it works. Thanks!
I am using BusObj 6.5 full client and the Oracle DB client as my middleware. I modified the oracle.prm file at C:\Program Files\Business Objects\BusinessObjects Enterprise 6\dataAccess\RDBMS\connectionServer\oracle and changed the following lines as such:
The results appeared the same. I started a new report from scratch, pulled in two date objects, and ran the report. When I went to Data->View Data, the dates all have 12:00:00AM stuck on the end.
I wonder if that’s the PRM file your machine is using. I seem to recall finding multiple PRM files on my PC. You may want to perform a search for PRM files.
I don’t understand why the dates would be causing your problem, though. I output dates with time to Excel without any problems. It’s possible there’s some problem with Excel / Office / Windows on your PC. Have you tried this on another computer?
I actually managed to figure out the overflow, but it was a typo that was too low, not too high. I’m still having this goofy problem with the time being appended under all conditions, but I will try searching for more PRM files.
Keep in mind that when you truncate a date, I believe Oracle still returns the full datetime - just truncated to midnight - if the column is a datetime column. The default is ‘DD’. You can change that - don’t know if that’ll cause the time to drop off or not. I don’t think it will, if the column is DATE. I think the PRM file will need to be changed.
See this page I found on the Internet for date TRUNC parameters:
Are you sure the applications aren’t formatting the date as they display it, though? The same way BO does when you display a date in a report. They might be automatically hiding the time portion of the date if the time value is zero, even though the database itself could still be returning the time.
I know I’ve been through all this once before, but it was awhile back and of course I’ve forgotten exactly how Oracle handles all of the datetime options. Like I said though, the time values shouldn’t make any difference if they’re all zero.
FWIW, I just did a select in TOAD against a column where some of the dates are stored truncated and some aren’t. The truncated dates are showing without any timestamp in the data grid in TOAD. The dates with a timestamp other than exactly midnight are showing with the timestamp. I suspect this is an application thing. In BO, they all have timestamps if you look at the data in the cube.
(BOB member since 2005-03-14)
(BOB member since 2003-03-12)
(BOB member since 2003-03-12)