There are certain software vendors out there that like to store their dates in numeric fields. This includes several SAGE accounting and ERP products. This poses a challenge when a user wants to create a report with a date or date range parameter.

One option is to use numeric parameters. This works fine but the user no longer gets to enter a normal date, and no longer gets a calendar control to use.

Another option is to write a formula to convert the numeric field into an equivalent date. This allows a date parameter in the selection formula, but it will usually make the report very slow. This is because it prevents the date criteria from being passed to WHERE clause in the SQL query. Crystal has to wait until ALL the data is returned from the database before it can convert the numbers into dates and apply the filter.

A better option is to create the parameter as a date data type, and write a formula to convert the date into the corresponding number. If you use the numeric formula field in the selection formula, Crystal will still be able to pass the value calculated to the WHERE clause. This is because the formula is tied to the parameter and can be calculated before the SQL is generated, unlike the first formula which is tied to the database field.

Here is a formula that converts a date parameter into an 8-digit numeric:

Local DateVar x := {?StartDate};
Year (x) * 10000 +
Month (x) * 100 +
day (x)

If you are using a date range you can write two formulas, one for the StartDate and one for the EndDate. They would be exactly like the formula above, except for the first line. The first line of each would be:

//This is the first line of the StartDate formula
Local DateVar x := Minimum( {?DateRange} );

//This is the first line of the EndDate formula
Local DateVar x := Maximum( {?DateRange} );