Crystal Reports to Informix DB. Error when searching within date range

99 Views

I am having problems with date range query from Crystal Reports (CR) to Informix DB. The query is going against a single table trying to find some info based on a date range. If the Date("...") function is used, it is generating a "convert(..)" method in the SQL that is not supported by Informix. Is there some other way so a "convert(...)" is not generated?

This SQL is being constructed by the third party module SAP crystal report.

Most databases (Oracle, SQL Server) do not have support for logical operation on DATETIME column with a string literal. So then they have to convert the string literal to DATETIME type by using CAST or CONVERT function. Eg: TOKEN_GENERATED2.lastrechargedate >= CONVERT(DATETIME,'2014-11-01 00:00:00', 120)

But, Informix supports logical operation on DATETIME column with a string literal. So no such extra mechanism of CAST or CONVERT is required, and instead a direct usage of string literal is fine. Eg: TOKEN_GENERATED2.lastrechargedate >= '2014-11-01 00:00:00'

So now when Crystal Reports sends that SQL with the "Convert(...)" function, Informix doesn't recognize and returns an error. "-201 SYNTAX ERROR WHEN PASSING DATE AS A PARAMETER "