How do I eliminate the time part in my date field?
In designer I had set up my object “Process Date” to Trunc(Process Date) but by doing so the cost of my query is very high (We use oracle 8.1.7 and I did an explain plan). If I remove TRUNC function, Designer will add the time part to it providing incorrect results. Any suggestions? Is there any option I can change in PRM file or in supervisor?.
Note: My table API_CLAIMS with the date column does not have time part stored in it.
If you are comparing the date part of the date with some field then for performance enhancement: (Of course in that case my earlier suggestion will not hold: that is just for display)
a)either store date part seperately in a date filed
b)for index usage create function based index on the process date
I have already formatted it as mm/dd/yyyy but when the user picks the date field in the query panel it shows:
Process Date between 4/1/2002 12:00:00 AM and 4/30/2002 12:00:00 AM
but when they select SQL button it shows the following sql (I have eliminated TRUNC function)
SELECT
IPS_REBATE.API_CLAIMS.ORGID,
SUM (IPS_REBATE.API_CLAIMS.CHARGE)
FROM
IPS_REBATE.API_CLAIMS
WHERE
(
IPS_REBATE.API_CLAIMS.NDCPROCDT BETWEEN '01-04-2002 00:00:00' AND '30-04-2002 00:00:00'
AND IPS_REBATE.API_CLAIMS.ORGID = '6223'
)
GROUP BY
IPS_REBATE.API_CLAIMS.ORGID
Why is there a discrepancy between what it displays in SQL and in Query panel?
I did something similar…I truncated the date in the object. When I create my prompt object I used the object in the select statement
@Select(Uo Web Log\Last Access)
and the following in the where clause…
@Select(Uo Web Log\Last Access) BETWEEN decode(ltrim(rtrim(@Prompt('Begin Date','D',,MONO,FREE))),'*',to_date('01/01/2000','mm/dd/yyyy'), @Prompt('Begin Date','D',,MONO,FREE)) AND decode(ltrim(rtrim(@Prompt('End Date','D',,MONO,FREE))), '*', sysdate,@Prompt('End Date','D',,MONO,FREE))
Doesn’t it kill my indexes? since when I do the conversion and check my SQL I see the cost of SQL is still very high. As I am aware in oracle, any functions on the left-hand side of the conditions will eliminate the use of indexes. I looked in ORACLE.SBO file and removed the timestamp in the “InputDateformat” and “InitDateformat” defaults, this still displays a timestamp in BO Query panel BUT doesn’t display any timestamp when you look at the SQL statement it generates…that doesn’t make sense to me
So I guess I now have to explain to users that it is a “BO Feature”
I don’t think that it would impact your indexes, since the solution he pointed you to only modified your List of values… the values that get sent to the database will be the dates, minus the timestamps (which is what you wanted at the beginning of this thread).
This does not apply any conversioning to the left side of your where clause.
FYI, Process Date object is not a list of values. This date is used to fetch all pharmacy claims ajudicated for the specified date range. This object is used in almost all the reports created by the users in the conditions section.
Sorry, I assumed you followed the suggested workaround of leaving your date alone and having the “LOV” for your prompt modified so that it wouldn’t bring in the timestamp. In that case, your submitted SQL would not include the time and your query results should fetch correctly.
So… Since that is not the case, what if you left the timestamp alone, and simply added a day to your “End Date”? Since the timestamp limits it to Midnight, you will get the full previous day?
Contract Begin Effective Dt=to_date(to_char(IW.ALL_CUSTOMER_SALES_AGG_SEC_VW.COMMITT_DATE, 'yyyymmdd'), 'yyyymmdd')
When I displayed the LOV values(I am not using LOV’s), It is showing 8/1/1993 12:00:00AM.
Also, In the reporter, I can see the timestamp in Results Section of Data Manager. My concern is to get only the Date part to compare two different dates. Is there any way that we can get rid of this timestamp?
Did you do a search on BOB? I believe this has been discussed recently…
What I usually do is have a Date object (in Oracle terms a truncated date, so that the time portion is set to 12:00:00 AM) and a “Date & Time” object in the universe, this way I can compare my date object to another truncated date object etc.
Regarding Oracle: Oracle dates always have a time portion (that is hours, minutes, and seconds) with it - in contrast to DB2, which is more flexible by offering date, time, and timestamp data types.
In Oracle you could convert a date to a CHAR and then do a Substr, but then your data type will be CHARACTER and not DATE anymore.
I’m having trouble following all the little tangents in this thread but . . .
Changing the sbo just changes the way the sql will generate but doesn’t change the fact that Oracle dates include an implicit (if not explicit) timestamp. So even if the date generates without the time stamp, midnight will be assumed.
You can explain this to users as a feature. Or you can create start date and end date objects which append the 00:00:00 and 23:59:59 timestamps to the entered date. Using TRUNC() will kill the index and isn’t “natural” for the user.
Yes. But you still run into the user training. If the user wants to see data for today and the index is built on TRUNC they have to be taught to enter 6/11 for the start date and 6/12 for the end date.
Okay, Dave, you got me! I put one day for the range because one day’s data is about all my feeble brain can grasp right now. But the point is really what if the date range is flexible and can be 2, 3, or more days, months, or years? Depending on the size of the universe and number of reports, adding objects for start and end date looks good to me. Alternatively, just train the users to learn about the time portion…
If your “date” object being compared is TRUNCATED, and the LOVs for the DATE are shown as truncated dates (using a more customized LOV that can be searched on), then your can have a START and END date even if you only want a single days worth of data.
A BETWEEN operator based on two equal values will give you that value. (e.g. JAN-01 is still between JAN-01 and JAN-01).
Most of our dates are stored without the timestamp in our tables and the indexes are based on this truncated time anyway. Unless you are trying to determine the number of hours between two times (like Support Center, etc), then usually a daily truncated date is good enough for evaluation on.