BusinessObjects Board

Eliminate Time portion

building semantic layer
#2

WHy donot you format that object in the universe as ‘mm-dd-yyyy’ or whatever date format you wnat.

Reema

reemagupta (BOB member since 2002-09-18)

#3

You may try the following but trunc() is usually the fastest way to do it !!

to_date(to_char(sysdate, 'yyyymmdd'), 'yyyymmdd')

Reema, your suggestion would change the formatting on the report but I believe it still use the timestamp on the db side…

avaksi :us: (BOB member since 2002-08-22)

#4

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

Reema

reemagupta (BOB member since 2002-09-18)

#5

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? :confused:

lbrewer (BOB member since 2002-08-15)

#6

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))

Eileen King :us: (BOB member since 2002-07-10)

#7

This has been discussed quite a bit. A quick search turned up this topic which might help.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#8

Dave,

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 :roll_eyes:

So I guess I now have to explain to users that it is a “BO Feature” :lol:

lbrewer (BOB member since 2002-08-15)

#9

How about …

create index my_index on my_table (trunc(my_date_column));

slimdave :uk: (BOB member since 2002-09-10)

#10

Latha,

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.

-RM

digpen :us: (BOB member since 2002-08-15)

#11

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.

lbrewer (BOB member since 2002-08-15)

#12

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?

-RM

digpen :us: (BOB member since 2002-08-15)

#13

Hi Avaksi,

I used the above code for one of my objects:

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?

Thanks,
Ravi

Ravi Amara :us: (BOB member since 2002-10-02)

#14

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.

Andreas :de: (BOB member since 2002-06-20)

#15

I’m having trouble following all the little tangents in this thread but . . .

  1. 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.

  2. 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.

Beth

Beth Katcher (BOB member since 2002-08-15)

#16

TRUNC(my_date) can be indexed in Oracle – see documentation.

slimdave :uk: (BOB member since 2002-09-10)

#17

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.

Beth Katcher (BOB member since 2002-08-15)

#18

The trunc() function removes the time. So all dates (times) from midnight to 11:59:59 PM will be returned as the same value.

To select a single day from a value processed with the trunc() function, then, would be a simple condition:

date = some_date_value

This will get every transaction for the “day”, no matter what the time was. No between would be required.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)

#19

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…

Beth Katcher (BOB member since 2002-08-15)

#20

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.

-RM

digpen :us: (BOB member since 2002-08-15)

#21

Getting back to the original topic…

Even if your date values are stored as truncated values, you will still see the time element on a list of values. The only solution(s) are given earlier in this topic (use a char version of the date for the LOV, a date version for the actual object).

It’s just something that has to be explained to the users. Here’s an experiment… create a query from Island Resorts using Invoice Date as a condition. Use the LOV to display a list of dates, and note that each one has 12:00:00 on it. Pick one. Now edit the condition by double-clicking on it, and attempt to change the time to 11:59:59 PM. You can’t. In this case, it appears that BusObj knows that there is no time element on the date, even though it insists on displaying it, and won’t let you use it as part of your query condition.

Dave

Dave Rathbun :us: (BOB member since 2002-06-06)