Crystal Reports Optional Date Parameter

[Former Member]

I have created a (Sybase) SQL query that allows for null parameter values in the where clause. I setup {dateParm} as a parameter in Crystal's Database Expert (Command) and embedded it in the SQL, like so:

select someColumn from someTable where (isnull({dateParm},getdate())=getdate() OR dt_column={dateParm})

I've made the parameter dynamic (provides all possible dates from the table) and OPTIONAL but when we leave the field blank zero records are returned. (Is there a better way to make parameters optional?)

If the parameter were a number my where clause would look like this:

...where (isnull({numParm},0)=0 OR {numParm}=0 OR num_column={numParm})
--assumes zero means nothing selected

If the parameter were a string my where clause would look like this

...where (isnull('{strParm}','zzz')='zzz' OR len(trim('{strParm}'))=0 OR str_column={strParm})

How do I make this work for a date?

PS Crystal Reports record selection formula is NOT an option here because there's a ton of data to pull then filter, better to pull only what we need from the DB

[vitaly_izmaylov]

What is the version of your Crystal Reports?

In the latest versions of Crystal Reports Designer "HasValue" function in Records Selection formula introduced to work with Optional Parameters.

Is there a reason to have Command Object connection?

[Former Member]

Crystal Reports 2016 Support Pack 5