string to date conversion error

[simpelli]

I am creating a report which is based on a command object. The query requires two parameters - "Begin Date" and "End Date" Because of the way the data is stored, we need to have these parameters within the query, not passed from the report.

Using Crystal 9
Informix database

The database field that is the target of the WHERE clause is a date/time STRING in the format 2003/12/16 09:00:00.00

The query contains the following clause:

WHERE
Date(table.dtime_field) >= {?Begin Ship Date}
and Date(table.dtime_field) <={?End Ship Date}

The report will run on one machine (a win2000 server) but not on the other (Win2000 Professional) Also will not run in ePortfolio on Enterprise 9

If I modify the command to:
WHERE
Date(table.dtime_field) >= '2003/12/16 09:00:00.00'
and Date(table.dtime_field)<='2003/12/16 09:00:00.00 '
it will process

If I wrap the parameters in quotes ('{?Begin Ship Date}'), I get a &quot;syntax error&quot;


What is the appropriate syntax to eliminate this error?

 

[synapsevampire]

Why &quot;we need to have these parameters within the query, not passed from the report&quot; ???

If you pass parameters from the report they will be nested in the SQL if properly constructed.

Use the record selection formula and check the Database->Show SQL Query to ensure that it's passed in the SQL.

-k

 

[simpelli]

More details....

We need the parameters within the query because the table is self-joined and we need to restrict the dates on the second instance. And it's a UNION query combining daily transactions and history transactions so filtering the dates out from the report selection criteria does not give us exactly what is needed.

SELECT
a.field1
a.field2
a.field3
b.field4
a.field5

FROM table1 a, table1 b

WHERE a.field1=b.field1
and
Date(b.dtime_field) >= {?Begin Ship Date}
and Date(b.dtime_field) <={?End Ship Date}

UNION

SELECT
a.field1
a.field2
a.field3
b.field4
a.field5

FROM table2 a, table2 b

WHERE a.field1=b.field1
and
Date(b.dtime_field) >= {?Begin Ship Date}
and Date(b.dtime_field) <={?End Ship Date}

Hope that's a litle more clear on the issue