Skip to Content
0
Dec 24, 2018 at 06:33 PM

Crystal Reports Optional Date Parameter

1437 Views

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