Converting a SQL Command Parameter from Date to Char

[former_member292966]

Hi,

I'm using CR XI version 11.0.0.1282. I've created a report using a SQL Command and have a parameter defined as Date. I need to convert this Date parameter to a CHAR and have created my WHERE clause as:

WHERE "CC_PAYH"."PPOST"= CHAR ('{?From Date}') AND

"CC_PAYH"."PPTYPE"<>13

I get a message saying 2008 is not a valid token which in AS400 means it doesn't like the year. Go figure.

I've tried running a similar query in AS400 and I get the desired affect. I've tried removing the parameter from the Where clause and putting it into the Select like:

CHAR ('{?From Date}') as MyTestDate

just to see the representation I would get and I get the same error.

The question I have like everyone else is; is this possible with this version of Crystal? I assume so since the same query will work in the database itself.

Thanks,

Brian

[former_member292966]

I found the ODBC driver doesn't support all the functions AS400 does or at least doesn't support them in the same way.

The driver expects the date to be in ISO format, once I figured that out I formatted the parameter accordingly and was able to use the CHAR function.

Thanks for the suggestions.

[Former Member]

Hi Brain,

Can you please try the query similar to the following syntax and verify whether it helps you!

Select * from Orders where ([Order Date]) =

CStr({?Date})

Regards,

Vinay

[former_member292966]

Hi Vinay,

Thanks for the reply but CSTR isn't in the AS400 Library as a function so it does not work.

Thanks,

Brian

[Former Member]

Do you have the "Cast" function ?