Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
SQL Query with DATE field |
Tue, Mar 13 2012 2:45 PM | Permanent Link |
Enrico Pettenati | 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 |
Wed, Mar 14 2012 4:00 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Enrico
Take out the DATE keyword if you're using parameters Roy Lambert [Team Elevate] |
Wed, Mar 14 2012 5:12 AM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Wed, Mar 14 2012 5:16 AM | Permanent Link |
Adam Brett Orixa Systems | 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; |
Wed, Mar 14 2012 6:07 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |