Input Parameter Box Date Format

Question

I have data sql statement which compares dates using some_date_to_compare > date('2011-05-05') syntax. The date is currently hardcoded in the SQL statement and I would like to change it, to pass the date as a parameter, like this: some_date_to_compare > date({?date_from}). The date_from is Date type, not Date/Hour.

The problem, however with this is that when I try to run the report Crystal passess the date from the Input Parameter box in the following format YYYY-MM-DD hh:mm:ss, so for example if I choose 2011-05-05 as a date from the calendar I get 2011-05-05 00:00:00 passed to date_from parameter.

Is there any way to have a workaround, or to pass the date in format that I expect (YYYY-MM-DD)?

EDIT (some additional informations):

I'm using Informix and the column is of DATE type (not DATETIME - date + time), so it's only date. Now the part of my sql is as follows: date_to_compare between date({?date_from}) AND date({?date_to}), so i'm trying to compare value from column in my table against date range.

The problem however is that when Crystal passess the date from Input Parameter Box as YYYY-MM-DD hh:mm:ss the result of query validation is: String to date conversion error: -1218, and it's because of the time part in the passed date.

Btw, is there any possibility to turn the query validation off by Crystal in the SQL command modification window?

Answer 1

The ToText() function can be used to do this.

ToText({datefield}, "yyyy-MM-dd")