Date Prompt - How do I get the start and end date from a date prompt?

[Miss Pris]

I want to use a date prompt using a range.  A filter using this would be :
CAST(date_key,date) in_range ?p_date?, if I use this filter it works fine but it is so slow because the filter ruins my INDEX on the table I use.

The Cast makes the query turn out like below :
   AND TRUNC ("Q6"."DATE_KEY") >= DATE '2007-12-07'
   AND TRUNC ("Q6"."DATE_KEY") <= DATE '2007-12-17'

The TRUNC makes the query time blow out to about 20 minutes from 5 seconds.  The query will not work without the CAST.

My query is using Oracle database 9, how can I use this filter without this problem occurring.  Can I get the last date from the date prompt p_date? 

A macro prompt would be good
ie.
and (d.date_key between #prompt('p_date')# and #prompt('p_date')# )

the second date being the last date in p_date if you know what I mean.

[FatCat]

Instead of using the cast command use one of the Oracle Date functions that are vendor specific in the function list.

This will make the DB do all the work.

[anirudha]

Just to check if I got it right?
Can you use two different parameters instead of one. In that case 'between' will work just fine.

[almeids]

If your date column doesn't include time, changing the data type from "Date Time" to "Date" in FM may help.
If it does, the TRUNC is necessary for the range check assuming you want it end-date inclusive...in that case an option would be to switch to or add an Oracle function-based index which includes trunc(date_key) instead of the raw column.