A few years ago I wrote a blog post about creating a date range parameter when your date field was stored in numeric values (eg. 20170614). I found that the best approach was to create a normal date range parameter, write formulas to convert the begin and end dates into numeric values and then use those two formulas in the selection formula. This gives the user a familiar calendar interface but still passes index eligible values to the SQL query.

Recently I faced a similar problem, but in this case the customer’s database stored the dates as strings. They were using a formula to convert the string values from the database into a true date and then they used that in the selection formula. The problem is that this criteria can’t be passed to the SQL, so the database ends up sending back ALL of the dates to Crystal. Then Crystal has to select the correct dates locally.

I changed the report to follow the same model as above. I used the existing Date Range parameter but wrote two formulas to convert the begin and end dates from the parameter into corresponding strings.  The formulas to convert the parameter values looked like this:

//BeginDate:
Totext (Minimum ({?Date Range}), 'yyyyMMdd')

//EndDate:
Totext (Maximum ({?Date Range}), 'yyyyMMdd')

Then our selection formula was:

{GL_MASTER.LAST_DATE_CHG} in {@BeginDate} to {@EndDate}

The users didn’t see any differences when selecting dates, but they certainly saw a difference in the performance of the report.