string to date conversion error

[simpelli]

I'm creating a Crystal Report (v9) against an informix db using a command object (query) with two date parameters. I'm having trpuble with the WHERE clause in my query:

"WHERE DATE(tbl.datetimefield) >= {?Begin Date}
AND DATE(tbl.datetimefield) <= {?End Date}&quot;

tbl.datetimefield is stored as &quot;yyyy-mm-dd hh:mm:ss&quot;

I keep getting a &quot;string to date conversion error&quot; message when attempting to validate the query.

Occasionally I'll get &quot;non-numeric characters in date time string&quot;


Any help?

 

[vpshriyan]

Hi,

You have enclosed your date variable with { ie, curly bracket, instead use single or double quotes.

An example below demonstrates various syntax:
create temp table x (t1 datetime year to second) with no log;
insert into x values ( current );
select t1 from x where t1 <= date('17122003');
select t1 from x where date(t1) <= '17122003';
select t1 from x where t1 <= extend(date('17122003'),year to second);

Regards,
Shriyan