WildCard in the Date range prompt

[Former Member]

Hi,

I have a date prompt like this :

@Select(emp\edate) BETWEEN @Prompt('1.Start Date','D',,Mono,free) AND @Prompt('2.End Date','D',,Mono,free)

Now, users would like to

---> enter a wildcard for the start date and enter "8/24/2009" (as an example) so they get all the dates upto 8/24/2009.

---> They can also enter a wildcard for the end date and a start date of say, "2/25/2009" and get all dates starting from 2/25/2009.

---> They can also enter wildcard for both to get all the dates in the table.

How can i do this in the designer? Please help.

Thanks!

[Former Member · Accepted solution]

Hi,

Give this a try..

(@Select(emp\edate) >= to_date(@Prompt('1.Start Date','A',,Mono,free),<FORMAT>) OR @Select(emp\edate) >= decode(@Prompt('1.Start Date','A',,Mono,free),*,Select sysdate from dual)) 

If this works then you can add a similar condition to the same.

Hope it helps you..

Regards,

Rohit

Edited by: rohit12 on Sep 1, 2009 9:56 AM

[Former Member]

Hi Rohit,

Thanks much for the input! My database is DB2 and this is the fist time,i 'm working on it.

I'm having hard time trying to figure out the equivalents of functions like DECODE.

Also, another problem is : We have many nulls in the date column and the user wants to see the nulls as well.

Thanks again!

Sarma

[Former Member]

Hi,

I am from the oracle back-ground and doesn't know about the DB2. But anyways I think CASE Statement is available in DB2.

Try implementing the same condition using CASE statement as both gives the samilar functionality.

Regards,

Rohit