I created a object on Universe level as Dt and kept format as "mm/dd/yyyy"...When I pull this object in report as condition,it shows me the list of values as
4/10/2005 12:00:00 AM
4/11/2005 12:00:00 AM
4/13/2005 12:00:00 AM
.
.
and so on… Now my client asks me to show those values in LOV as only
4/10/2005
4/11/2005
4/13/2005 …
I went through the link you sent me…There are 2 option available:
1.) Change date to character in Universe to remove time-stamp…This will not work for me as I cannot apply sorting on this
2.) Create new object for date as character type and do as said by Digpen …
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:
Code:
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.
This looks good but there is a problem…Here,I am getting for my date LOV’s without time stamp as
4/12/2005
4/13/2005.....
But in my report condition panel when I say
Date equal to …
and when I select a value from this list of values…I am getting as
Date equal to 4/12/2005 12:00:00AM
Is there a way to avoid time-stamp in condition panel also…like
You did not say what type of database you are querying but here’s a two things I found in a couple of our universes. First in Teradata we just added a (date) function to a timestamp object as follows: LST_UPDT_EV_TS (date). This does exactly what you want.
In an Oracle universe I found an object that I think did about the same thing. My password is not current so I could not test but this it the object:
to_number(to_char((CHRGOF_DT ),‘YYYYMM’)). Give them a try.
Thanks dflood…I am using SQL Server Database and BO 6.1.3…
I need the format as MM/DD/YYYY...But the formula you mentioned in Oracle is converting Date to number...
This will be a problem since I need the format as MM/DD/YYYY and also I use sorting on this object in reports...
Please let me know if I am wrong in what you explained…I am getting the required format i.e., Dates without time-stamp in LOV’s and with date type…but I cannot get that in Condition panel…Steve says “this is not possible”…
With SQL Server, I do not know of a way to do what you want. Once you label an object a timestamp, the database wants the time at the end. I do not know of a way to parse it out at the universe level and still allow you to sort it as a date object. My 2 cents would be to get with the DBAs and ask if a new column can be added with this same field but not as a timestamp field.
(BOB member since 2002-06-06)
(BOB member since 2005-09-21)