Group,
I know this is a real simple one, but I’m all twisted around trying to get this thing worked out . Here is what I got:
Oracle 8.17
BO 5.14
I have a LOV for a Date Dimension Object in a Universe called FULL_DATE. I need to have this Date Object show 10/24/2002 or ‘MM/DD/YYYY’ when the user is prompted in a report. However, the prompt LOV shows 10/24/2002 12:00:00AM.
I need to supress the Time stamp part of the date that the LOV brings in from the Oracle Table. I’ve tried different combinations of TO_CHAR and TO_DATE, but with no luck :x .
If I use TO_CHAR(FULL_DATE,‘MM/DD/YYYY’) it removed the Time Stamp of the data, but I loose my sort order since it is no longer a Date format.
I know this is simpe, any sample code would be appreciated.
-Dave
That doesn’t affect the LOV display however. Every date field in BusinessObjects displays the time in the LOV. The only way around that is to convert the data into a character string in the format of a date, which generally means you need to set up a custom LOV query. You generally want your database fields to still be dates.
Same thing should happen. If you have a date object in your universe, pull up the LOV from the query panel, and look at the values. The same think works in Island Resorts if you turn on the LOV for Invoice Date. You have to turn it on in Designer because it is turned off, but you can turn it on to test.
Which brings me to another point, which I should have made earlier, is that date LOV’s are usually a waste of time anyway. Unless there is a specific set of dates (month end, for example) then the user is better off just typing in the date value.
TRUNCATE = “Removes all rows from a table or cluster” From my SQL book… can you explain what this will do again and how it relates to my TIME and DATE problem?
Like Dave R. Mentioned it is this little extra “Feature” that is buggin me… actually my users… I guess I could get rid of the LOV…
Truncate works… but at the database level. It will remove the time. The issue isn’t a database issue at all, it’s a BusObj issue. It just works that way. Every date LOV has a time, no matter what the actual value from the database is. That’s why I said you had to make a custom LOV where the date is converted to a character that looks like a date.
Better advice is to remove the LOV for all date objects.
I believe you can still give this to work, but leaving your object as a DATE object, but modifying the LOV SQL to do a to_char(tab.DateObject, ‘MM/DD/YYYY’)
The LOV will be a character representation, but since you select the “Date” version of the object, it should work fine. If not, you create a “Condition” object like:
tab.DateObject = to_date(@Prompt('What is your Date?','A','Class\Object',mono,free),'MM/DD/YYYY')
Essentially, turning the results of the character back into a date, but as I said, I don’t think it’s required. You just need to adjust the SQL that returns the LOV. Don’t forget to prevent BO from regenerating the SQL.
If you do this, you’ll have to alter the sort as well. To get dates to sort in the proper order in character form you will need to format them as YYYY/MM/DD. Otherwise the sort is wrong.
This is my opinion, but I would consider this a bad practice for universe design. Or report design, for that matter. There is nothing visible on the query that tells you that this option has been checked. And it’s a perfect example of what I call the “It worked yesterday” syndrome. A universe designer changes something that breaks something else, and has no idea what went wrong. Just my two cents.
Why would you have a LOV for dates anyway?
LOV’s for dates are of little value to the end-user (unless you have very specific dates you want the end-user to select from).
digpen is nearly right but Dave has a point in not hardcoding the SQL. Try if the following helps…
Create a new object to_char(Date, ‘yyymmdd’) and change your orig object to to_char(Date, ‘mm/dd/yyyy’). Then in the LOV of the original object sort it by the new object. That way no hardcoding, u get the dates in order and no timestamp is shown!!
It makes sense, but will also kill any indexes on the date values. And since dates are very often used in conditions… which is the entire point of haing a LOV query afterall … then you want to be sure to have an index available for use if there is one.
I do believe in Oracle (8.x or higher) one could create a function index on to_char(Date object, ‘String Format’)…still I am not favoring LOVs for Dates in general.
The table that holds the FULL_DATE and has values like 12/31/9999. So the dates are not natural…So the LOV is nice for them to see which potential dates they need to use.
You’re right about it being poor universe design, and it was a quick patch. For a “real” solution, you can create an LOV object and use its LOV.
To Create an LOV that appears like a date (minus the time) and sorts like a date, yet is a Character, use the following LOV SQL.
I built an object called INV_DT_LOV which is a character object.
The LOV Name is customized to: INV_DT
and the SQL is:
SELECT inv_dt
FROM(
SELECT DISTINCT
to_char(Sales.invoice_date,'mm/dd/yyyy') as inv_dt
FROM sales
) order by to_date(inv_dt,'mm/dd/yyyy')
You have to use a sub-select, since you can’t apply the ORDER BY to the SELECT DISTINCT query.
I force this SQL to not regenerate, and build my LOV object.
For my INVOICE DATE object, I just use my Customized INV_DT LOV, built by the hidden INV_DT_LOV object.
Make sense? This prevents me from modifying my original object (other than changing the LOV name) and keeps my LOVs in a special LOV generating folder along with all of my other LOVs. So… If I ever need to changes LOVs, I don’t bother with the original objects, just the LOV object defs.
"You have to use a sub-select, since you can’t apply the ORDER BY to the SELECT DISTINCT query. "
Why can I not apply “ORDER BY” to the SELECT DISTINCT clause. I did it in the last project. Please tell me why, I might have to go back and correct something atleast e-mail them.
It was working fine there. Or are we talking about just the Date LOV in this context.
thanks,
Just to add to this topic, the way to use Digpen’s solution in SQL server.
Thnx Digipen
The main difference is having to use an alias in the sub select, in this case ‘a’…
select INVDate
from (
select distinct convert(varchar(10),INVOICE_DATE,103) AS INVDate
from dbo.INVOICE_DATE
) a
order by convert(datetime,INVDate,103)
. Here is what I got:
(BOB member since 2002-07-10)
It will remove the time, if there was one, but the LOV values will still show 12:00:00 AM for every date. It’s a “feature”. 
(BOB member since 2002-06-20)
(BOB member since 2005-01-06)