I've some problem with Field type DATE in SQL Query.
I'm writing the following SQL inside a TDDBQuery:
SELECT SUM(DETT_IMPORTO) AS TotalCosto
FROM VDettNumb
WHERE DETT_DATE BETWEEN DATE '2012-01-09' AND DATE '2012-01-09'

all works fine, but i want to pass a different DATE so i write:
SELECT SUM(DETT_IMPORTO) AS TotalCosto
FROM VDettNumb
WHERE DETT_DATE BETWEEN DATE :Value01 AND DATE :Value02

but when i try to open the Query the applcation return me:
ElevateDB Error #700 An error was found in the statment at line 3 and column 30 (Expected date constant but instead found ?).
I tried also to set the parameters type as ftDate.

Excuse me if the question is stupid but i new on dbElevate, i'm using 2.08 on Delphi X2

Thanks
Enrico

Enrico


Take out the DATE keyword if you're using parameters

Roy Lambert [Team Elevate]

Enrico:

>>Take out the DATE keyword if you're using parameters

Or leave in the DATE keyword, but pass in a string ...

--

The following will not work:

EDBQuery1.SQL.Text:= 'SELECT SUM(DETT_IMPORTO) AS TotalCosto FROM VDettNumb WHERE DETT_DATE BETWEEN DATE :Value01 AND DATE :Value02';
EDBQuery1.SQL.Text.Prepare;
EDBQuery1.ParamByName('Value01').asDatetime:= Date - 30;
EDBQuery1.ParamByName('Value02').asDatetime:= Date;
EDBQuery1.Open;

--

However either of these will:

EDBQuery1.SQL.Text:= 'SELECT SUM(DETT_IMPORTO) AS TotalCosto FROM VDettNumb WHERE DETT_DATE BETWEEN :Value01 AND :Value02';
EDBQuery1.SQL.Text.Prepare;
EDBQuery1.ParamByName('Value01').asDatetime:= Date - 30;
EDBQuery1.ParamByName('Value02').asDatetime:= Date;
EDBQuery1.Open;

EDBQuery1.SQL.Text:= 'SELECT SUM(DETT_IMPORTO) AS TotalCosto FROM VDettNumb WHERE DETT_DATE BETWEEN '':Value01'' AND '':Value02'' ';
EDBQuery1.SQL.Text.Prepare;
EDBQuery1.ParamByName('Value01').asDatetime:= '2012-02-13';
EDBQuery1.ParamByName('Value02').asDatetime:= '2012-03-13';
EDBQuery1.Open;

--

EDB does lovely stuff with coping with the datatypes of Params, so you don't have to. This makes passing data in & out of the DB easy & quick.

For example you can pass in a MEMO field (even one which contains many quotation marks) as a Memo param, or  BLOB data (images, jpgs etc.) without having to do any ugly management of the data.

I hope you are enjoying XE2.

D'Oh,

Last one should read:

EDBQuery1.SQL.Text:= 'SELECT SUM(DETT_IMPORTO) AS TotalCosto FROM VDettNumb WHERE DETT_DATE BETWEEN DATE '':Value01'' AND DATE '':Value02'' ';
EDBQuery1.SQL.Text.Prepare;
EDBQuery1.ParamByName('Value01').asDatetime:= '2012-02-13';
EDBQuery1.ParamByName('Value02').asDatetime:= '2012-03-13';
EDBQuery1.Open;

Adam


I belive you do a loit of stuff over the internet. If I'm right can you have a look at Paul Waegemans' post in DBISAM general and give Paul the benefit of your experience please.

Roy Lambert