SQL Error converting datetime

[2005 IR Help]

By: ladylistst - ladylistst

SQL Error converting datetime

2004-11-24 09:33

Hello,

 

I am using iReport to build my reports. I have 4 parameters (Strings) that I am using in my query. 2 of the parameters are meant to be dates (ie. "1/1/2004"). When the 2 'date' parameters preceed the other 2 string parameters, I get the error:

 

SQL problems: Syntax error converting datetime from character string

 

The where statement that generates this error is:

 

WHERE (date between $P{after} AND $P{before}) and pract_number = $P{pract} and payee_number = $P{payee}

 

The one that works is:

 

WHERE pract_number = $P{pract} and payee_number = $P{payee}and (date between $P{after} AND $P{before})

 

This isn't as big of a deal except when I use a subquery in my select statement that uses the same set of parameters. An example of a query that fails is:

 

 

SELECT person.lname AS phyLname, person.fname AS phyFname, bill.bill_id,

(SELECT COUNT(bill.bill_id) AS billCountFROM app INNER JOIN

phy ON app.phy_id = phy.phy_id INNER JOIN

claim ON app.app_id = claim.app_id INNER JOIN

person ON phy.person_id = person.person_id INNER JOIN

bill ON claim.claim_id = bill.claim_id INNER JOIN

WHERE (phy.payee_number = $P{payee}) AND (phy.pract_number = $P{pract})

and (date between $P{after} AND $P{before})) as totalBills

FROM app INNER JOIN

phy ON app.phy_id = phy.phy_id INNER JOIN

claim ON app.app_id = claim.app_id INNER JOIN

person ON phy.person_id = person.person_id INNER JOIN

bill ON claim.claim_id = bill.claim_id INNER JOIN

WHERE (phy.payee_number = $P{payee}) AND (phy.pract_number = $P{pract}) and

(date between $P{after} AND $P{before})

ORDER BY person.lname, bill_procedure.procedure_code_id

 

Does anyone have any ideas how I can make this query work?

 

Thanks