Date prompt Optional

[sandy_vitty]

 Hi All
      I have a requirement in which the user wants a date prompt(from date and to date).
      He wants it to be optional in such that if he can choose the "from date" and selecting "to date"is optional.
      If he does not select it has to default to sysdate.
      So i wrote a query in the filter condition
     
      case when ?to_date? is null then
      date between ? from_date? and sysdate()
      else
      date beween ?from_date? and ?to_date?
     
      But this gives me error.
     
      Any help to achieve this would be appreciated.
     
  Thanks
 

[wyconian]

Hi

You can't use a conditional statement (case statement or if then else) in a filter.  You need to say something like

(start date = ?p_start_date? and ?p_end_date? is not null and end_date = ?p_end_date) or (start_date = ?p_start_date? and ?p_end_date? is null and end_date = sysdate)

If you use the calendar prompt icon you're user will need to untick the box at the top left of the prompt to not use the value.

Good luck