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")

Answer 2

It appears to be a case related with the connection type. Earlier I used simple JDBC connection. Now, when I'm using ODBC connection (Informix driver) the problem with the date format is gone.

Answer 3

Create a blank report and Insert the original report as a Subreport. Now in the Main report, create a static parameter of 'Date' type. Create a new Formula called 'Date_conv' and type this:

Datetime({?Date_parameter})

Then place the subreport on the main report. Right-click the subreport and select Change Subrpeort Links. Select the formula and move it to the Links to area. Next, from the drop-down on the bottom left select the datetime parameter of the subreport.The Report should now prompt for a 'Date' instead of the Date time. Refer the site - http://scn.sap.com/thread/2085118