Date Condition

building reports desktopintelligence
1

BO 6.5
Oracle 8i

Not sure if this belongs here or in the BCA thread…

I have a BO report that has a date field in it called “Updated Date”. It is a DateTimeStamp (11/18/2004 01:32:31 AM). I dont really care about the time…just the date. I need to schedule the report to run every night that pulls data where the “Updated Date” is equal to the previous date. Meaning, when the report runs on Tuesday (scheduled at 1am) it pulls only data that has an “Updated Date” = Monday.

I know how to send reports to BCA. My problem is setting the condition to pull the previous days data. I have tried several conditions(too many to list) but always get “Condition not met” error even though the data is there.

Can someone help me out?

Thanks.

d1cjm1 (BOB member since 2003-02-24)

2

Assuming that the issue is getting the date to compare, there are a few threads on the forum with that same problem. Try searching on Oracle Date timestamp. You could also take a look at this

charlie :us: (BOB member since 2002-08-20)

3

I have read thru most of what is on the board but cant find exactly what I need. The issue is with comparing the date and I am pretty confident the timestamp is throwing off the comparison. When I send the report to BCA to run I create the following condition:

==CurrentDate() -1

In Designer, UpdatedDate is defined as follows:

trunc(table.UpdatedDate)

Am I even close?

d1cjm1 (BOB member since 2003-02-24)

4

You’re close. TRUNC sets the time part of a date column to 00:00:00; i.e., the start of the day. So UpdatedDate looks something like 23-NOV-2004 00:00:00. I think what you need to do is get rid of the time portion, so in Designer, define UpdatedDate with something like

TRUNC (table.UpdatedDate, 'yyyymmdd')

Then your comparison will have a better chance of working.

charlie :us: (BOB member since 2002-08-20)