I have a Crystal report (version XI r3) that uses a SQL command object to retrieve its data. In the command object I have a parameter for a date. My database uses "date" fields stored as numeric values in YYYYMMDD format, so I've specified the parameter as numeric and added a prompt to say "enter date in YYYYMMDD format".
My users don't much care for that; they want to be able to use the date-picker and/or to be able to enter the date in MM/DD/YYYY format.
My investigations so far have led me to believe that if I convert the parameter to a true date datatype, I won't be able to make use of it in the SQL command object because I can't convert it from a date to a number in the SQL statement, so I'd have to do my date-range control in the Crystal Select Wizard rather than in my SQL statement, which could slow my report down by an order of magnitude or two (since I'm hitting a table that is indexed by this date field, and that has a lot of records per day).
Am I wrong? Is there a way to let a user enter a date in MM/DD/YYYY format and still be able to use it as a numeric YYYYMMDD parameter in my SQL command object?
I'm afraid you would have to modify the original Command's to replace the numeric parameter with a date parameter, and do the conversion from date to number within the Command itself.
So, within the Command:
WHERE MyDate = {?MyNumberParam)
would become:
WHERE MyDate = (YEAR({?MyDateParam})*10000) + (MONTH({?MyDateParam})*100) + DAY({?MyDateParam})
The last part will convert 20th April 2012 to (2012*10000 + 4*100 + 20) = 20120420, which I believe is what you'd want.