Very Strange. Date parameter binding does not work sometimes on OraQuery.

[Black]

Hello there. I have very strange problem. I am using Delphi 2006 and ODAC 5.70.028.

I have following query in my OraQuery's SQL property.

select *
from mpos_transaction
where create_date between TO_DATE(:date1,'DD.MM.YYYY') AND
TO_DATE(:date2,'DD.MM.YYYY')

and i am trying to bind two date parameters by selecting from the form DateTimePicker component like this:

OraQuery1.Close;
OraQuery1.ParamByName('date1').asDate := cxDateEdit1.Date;
OraQuery1.ParamByName('date2').asDate := cxDateEdit2.Date;
OraQuery1.Open
although this query does not return any rows, if i dont use any parameter and directly enter the SQL into OraQuery, some rows return. Like that :

select *
from mpos_transaction
where create_date between TO_DATE('01.02.2007','DD.MM.YYYY') AND
TO_DATE('09.02.2007','DD.MM.YYYY')

Please note that i am sure that i am selecting same dates using DateTimePicker component and I am sure that there are rows between that date interval.

Besides I try that in other computers and i see that on some of the computers it is working very well. (All OS English Win2003).

Any suggestions ? I have look over the regional setting of thouse computers and they are all same. What can be the problem ??

Thanx for helping.

[Plash]

The first argument of the to_date function should be a string value. But you are passing a date value to this function. Probably this is the reason of the problem. Try to change the SQL property of TOraQuery component to the following:

Code: Select all

select * 
from mpos_transaction 
where create_date between :date1 and :date2