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

[Former Member]

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 "

[former_member292966]

Hi Ray,

If your field is defined as DateTime, try using the DateTime function instead like:

{TOKEN_GENERATED2.lastrechargedate} >= DateTime (2014, 11, 01, 0, 0, 0);

Or like:

{TOKEN_GENERATED2.lastrechargedate} >= cDateTime ('2014-11-01 00:00:00);

Crystal is specific when it comes to working with datatypes.  So if you are using a DateTime field, Crystal needs to use the appropriate function to evaluate against.

Thanks,

Brian

[Former Member]

Hi Brian,

We tried the solution you provided above but we are still seeing same issue. The crystal report is still adding convert() function and Informix is throwing syntax error.

What I have observed is :

The datetime values that we are passing to the crystal report are assigned to the report datetime fields and hence it is trying to apply convert.

However, when we use Informix ODBC and OLEDB combination we don't see any such error.

The error is observed when we use IBM Common Client ADO .Net driver and OLEDB.

Now there can be multiple ways to solve the problem:

- Change the report input field to string

- Find out a way to turn off conversion of value as we can use direct string with Informix

- Find out the right combination of drivers

Can you suggest on 2 and 3 or if there is any industry standard solution available.

Also, I am wondering how crystal report works with DB2 in such case as DB2 also doesn't have convert() function.

[former_member292966]

Hi Vaibhav,

When it comes to data type and formats, Crystal relies on the database driver to manage the syntax.  This is why issues like this happen when a database and the database client are out of sync and the newer database has removed functionality the older client wants to use.  This is what's happening with SQL Server 2012.

I would not recommend trying to find a way to turn off the conversion.  That may cause more problems down the road.

I would say changing the report is a backup solution.  If you can't get anything else to work then do this.  I say this because if you have several reports you'll need to use the same method in all of them so maintenance can be an issue.

The best one would be to get a working combination of client and driver.  If that works then the other 2 become non-issues.

Good luck,

Brian

[Former Member]

Thanks Brian for your prompt response. I shall try Informix native .net driver and OLEDB today.

Meanwhile, I had one more question in same context. We did refer this doc http://resources.businessobjects.com/support/communityCS/FilesAndUpdates/dbex10win_en.pdf and found a Patch ID: 37836103 which seems to be addressing datetime related issue with Informix.

Description:

The Informix datetime column types Year to Second, Year to Minute, Year to Hour, Year to Day, Year to Month, and Year to Year are handled as strings by Crystal Reports.

New Behavior:

Crystal Reports handles Informix datetime column types as datetime entities.

Hence we upgraded to version 11 but still the problem persists.

Can you please help understand what this patch is exactly supposed to do.

Regards,

Vaibhav  Dantale

[former_member183750]

Hi Vaibhav

I am not sure how to square the link you reference (which is for CR 10) with upgrade to CR 11(?). I am also wondering where you found patch 37836103 as all patches for CR 10 and CR 11 have been removed from all SAP sites (as far as I am aware anyhow).

Now, Crystal reports version 11 does not really tell me much as people refer to CR v 11.0, v. 11.5 and CR 2011 (v. 14.0) as CR 11. So I need more detail re. the actual version of CR you are using. Please find that in the CR designer Help | About and quote that.

- Ludek

Senior Support Engineer AGS Product Support, Global Support Center Canada

Follow us on Twitter