I did the Metzker method to create a Date LOV with no timestamps for my customers prompt.
Testing as a report writer- I created a prompt -where my date between say- 2/1/2007 and 2/20/2007 and it does not return any rows from 2/20/2007. (and there is data present in the db) I discovered the reason for this when, instead I used hardcoded dates in the where condition- The completed condition appear with midnight timestamps on the dates- and so I return nothing from my ending date.
What’s the best method to be able to include the ending date? I can think of a couple-
word the prompt as such and train the user to pick the day after the desired end date…not such a good idea it’s counterintuitive and they’d fuss.
make a second object for ending date with some formula to add 23:59:59-still keeping my prompt LOV to just the date…and what would that be in oracle? How does a prompt go together when a between uses two different objects?
any other technique?
I’m confusing myself now. Any help?
I’m BO 6.5.1
Build a predefined condition for your end-date prompt. Based on your database platform, truncate the time portion off, add a day, subtract a second. Or some other permutation or variation that works for you.
I haven’t done this with datetime fields, and I don’t know which database platform you are on – but I’ll show you an alternative situation where I did a calculation based on a prompt to get a predefined condition.
I had a situation where I had three Data Providers. For one of them, I just wanted to return data for a given fiscal year. For the other two, I wanted to return data for that fiscal year plus the previous. I only wanted a single prompt for “which fiscal year”. I wanted to compute the previous fiscal year. Since prompts return alpha strings – I had to convert the result of the prompt to an integer, then subtract one “year”.
I had two predefined conditions (based on Sybase syntax) which I then dragged into Conditions panel for the latter two queries (and only the first of which was dragged in for the first query):
Current Fiscal Year prompt @Select(Calendar\Fiscal year) = @Prompt(‘Enter Fiscal Year (only one)’,‘N’,‘Calendar\FYAvail’,mono,free)
My db is oracle. I’m trying to make this concept work. I understand the idea of seeing one thing and making it something else under the covers. The timestamp is proving troublesome but I haven’t given up yet…
I’m not familiar with the Metzker method to create a Date, but if you trunc the date then your prompt should work as Oracle doesn’t take the time stamp into account.
So - trunc ( date, [ format ] )
If the format parameter is omitted, the trunc function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.
Then you between 2/1/2007 and 2/20/2007 will include the beginning and end dates.
This is the solution that I came up with using Oracle and BOBJ6.5
Thanks Anita- you sent me in the right direction using the where clause.
The requirement: Date range prompts without the timestamp and included all 24 hours of the Ending Date (in spite of the prompt having a hidden midnight timestamp on it).
Our customer required prompts for Date ranges and only the date (minus the timestamp) show up in a prompt box. The date range could span year ends- so it needed to be a date, not character. Also, the customer did not want to use the format yyyymmdd, which would have sorted a character field correctly. So it must be a date datatype. BOBJ always puts a timestamp on a Date datatype object, whether it comes from the datasource or whether it defaults to midnight.
I went to these lengths to create prompt objects
Create hidden character object from SourceDtt Called MyDateLOV (thank you Mr. Metzker)
On the properties page- Rename the LOV to be memorable STDDT1 and select Export with universe.
Edit the LOV SQL- to be
Select DATEOBJ from
(
Select Distinct
To_char(Table.Dtt,mm/dd/yyyy) as DATEOBJ
FROM
Table
)
Order by to_date(DATEOBJ,mm/dd/yyyy)
Be sure to Check Do not generate SQL before running
This makes a character object but is sorted like a date object. Display the LOV to verify this.
Create a Date object from SourceDtt, called MyDate and replace the generated LOV with the LOV STDDT1 from the character object MyDateLOV
Select Export with universe. (The edit LOV will show the code from MyDateLOV and will have Do not regenerate SQL . ) This object has a default timestamp of midnight you will see it if you create a where condition using a single date from the Show Values. The show values uses the Character LOV, but MyDate- being a Date datatype will fill in the timestamp.
Create a Date object to be used for Beginning Date Prompt
On the definitions tab- Date datatype, Select SourceDtt WHERE
SourceDtt > @prompt(Beginning Date?,D,Class.My Date,mono,free)
On the properties tab- replace the LOV with STDDT1 and export with universe.
This grabs all SourceDTT greater than MyDate (which has the hidden midnight timestamp).
Created an object to be used for Ending Date PromptOn the definitions tab- Date datatype, Select SourceDtt WHERE
SourceDtt -1 < @prompt(Ending Date?,D,Class.My Date,mono,free)
On the properties tab- replace the LOV with STDDT1 and export with universe.
This is a bit of convoluted logic that grabs all SourceDTT where SourceDtt minus 1 is less than MyDate (which has a midnight timestamp of the desired ending date.)
Save (and export) the universe.
Build a report using MyDate and creating a condition that includes where MyDate between Beginning Date Prompt and Ending Date Prompt.
I haven’t found a hole in this yet…it seems to work.
@benslow
Thank You for such a great workaround but I am facing a problem after doing your suggested workaround. I am getting no data to retrieve error after using my date between condition. Please suggest…
It’s been 2 years…and after my work - I don’t think my users applied it.
I did notice I mispoke in my post-
Be sure that you are
Using these objects in the results area- not the conditions area.
Example below- I used the DTT as a result and put the Begin prompt object and the End prompt object in the results also.
The SQL code will show the object 3 times in the results and also in the where condition(you didn’t put it there).
Beginning and end date cannot be the same date-because of the coding. (the -1 and <) So if you want one day - Say Mar 10th, use Begin date of Mar 10 and end date of Mar 11. If you have to word the prompt to guide the user say something like “Date up to, but not including:”
SELECT
Table.STRD_DTT,
Table.STRD_DTT,
Table.STRD_DTT
FROM
Table
WHERE
( Table.STRD_DTT >= @Prompt('Beginning Strd Date?','D','Class\Strd Date',mono,free) )
AND ( (Table.STRD_DTT -1)< @Prompt('Ending Strd Date?','D','Ld Class\Strd Date',mono,free) )
(BOB member since 2005-11-04)
(BOB member since 2006-06-01)