Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 10 total |
Query Between Date Time Stamps |
Thu, Dec 15 2011 8:13 PM | Permanent Link |
John Postnikoff | Under DBISAM I used the below code to extract data between two date-time stamp fields. This does not work under ElevateDB. I am wondering how I can make it work. I am not too familiar with casting and have had trouble trying other methods. I did double check my regional setting for the following format. yyyy-mm-dd hh:mm:ss AM . I noticed with that the precision time-stamp has four millisecond digits where there may have to be further adjustment, and which I would also like to use. //used when adding calendar date picker with DBISAM date-time stamp fields with seconds and AM or PM. Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker1.time); Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker2.time); // SQL between my DBISAM date-time stamps - works nice DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + 'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; Any help is appreciated to get the above working with ElevateDB. John Postnikoff. |
Fri, Dec 16 2011 1:39 AM | Permanent Link |
Adam Brett Orixa Systems | John
>>DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + >>'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; Dates are no longer referenced as strings in EDB. A bit of a bind if you are used to free-and-easier DBISAM ... but a good thing once you understand concepts like INTERVALs ... which bring a lot of power. Your code will be much closer to working if you used the following in place of your existing code: ' SELECT * From Shippers WHERE ORDERDATE > CAST(' + QuotedStr(Edit1.Text) + ' AS TIMESTAMP) Note that I have simply wrapped your previous String injection with: CAST('+YourString+' as Timestamp) I don't wish to be an old grandmother ... but I would suggest 2 other possible improvements: Use FORMAT('SELECT* From Shippers WHERE ORDERDATE > CAST('%s' AS TIMESTAMP) ', [QuotedStr(Edit1.Text)]) ... using the %s just makes things easier to read. I would also suggest considering using Params in place of String fangling, i.e. MyQuery.SQL:= 'SELECT* From Shippers WHERE ORDERDATE > :DateParam' MyQuery.Prepare; MyQuery.ParamByName('DateParam').asDateTime:= StrToDate(Edit1.Text); I know this is longer, but: You now test whether the SQL will prepare in code ... saving time with badly written SQL, and you pass in your params in a type-safe way which leaves it to EDB to convert the data into the correct data-format. I hope this is useful & not too hectoring. Adam |
Fri, Dec 16 2011 4:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
I agree with Adam about using a parameterised query being the best approach. If you don't go down that route I wouldn't use CAST but simple preface the correctly formatted and quoted timestamp string with the keyword TIMESTAMP eg DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP ' + QuotedStr(Edit1.Text) + 'and orderdate < TIMESTAMP ' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; INTERVALs are very powerful but can take a bit of getting your head round. Roy Lambert [Team Elevate] |
Sat, Dec 17 2011 10:08 AM | Permanent Link |
John Postnikoff | Thanks guys for the feedback. I know have a much to learn in this area, along with using parameters. As always I very much appreciate the great support, which is the reason I am here. Sometimes its hard to train an old dog like me new tricks.
John Adam Brett wrote: John >>DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + >>'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; Dates are no longer referenced as strings in EDB. A bit of a bind if you are used to free-and-easier DBISAM ... but a good thing once you understand concepts like INTERVALs ... which bring a lot of power. Your code will be much closer to working if you used the following in place of your existing code: ' SELECT * From Shippers WHERE ORDERDATE > CAST(' + QuotedStr(Edit1.Text) + ' AS TIMESTAMP) Note that I have simply wrapped your previous String injection with: CAST('+YourString+' as Timestamp) I don't wish to be an old grandmother ... but I would suggest 2 other possible improvements: Use FORMAT('SELECT* From Shippers WHERE ORDERDATE > CAST('%s' AS TIMESTAMP) ', [QuotedStr(Edit1.Text)]) ... using the %s just makes things easier to read. I would also suggest considering using Params in place of String fangling, i.e. MyQuery.SQL:= 'SELECT* From Shippers WHERE ORDERDATE > :DateParam' MyQuery.Prepare; MyQuery.ParamByName('DateParam').asDateTime:= StrToDate(Edit1.Text); I know this is longer, but: You now test whether the SQL will prepare in code ... saving time with badly written SQL, and you pass in your params in a type-safe way which leaves it to EDB to convert the data into the correct data-format. I hope this is useful & not too hectoring. Adam |
Tue, Dec 20 2011 7:50 PM | Permanent Link |
John Postnikoff | I am wondering if you can look at this again. It does not seem to work. If I try to the other method using CAST I get a Elevate DB 1011 conversion error. I have not tried to use parameters just yet and was hoping to get what you suggested below working. I am trying to get a better handle on intervals and am wondering if you can direct me to some reference that I can study in detail.
Roy Lambert wrote: John I agree with Adam about using a parameterised query being the best approach. If you don't go down that route I wouldn't use CAST but simple preface the correctly formatted and quoted timestamp string with the keyword TIMESTAMP eg DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP ' + QuotedStr(Edit1.Text) + 'and orderdate < TIMESTAMP ' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; INTERVALs are very powerful but can take a bit of getting your head round. Roy Lambert [Team Elevate] |
Wed, Dec 21 2011 4:47 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | John
EDBManager is your friend. I just tested SELECT _HideAlarm FROM Calls WHERE _HideAlarm > TIMESTAMP '2011-01-01 12:00' AND _HideAlarm < TIMESTAMP '2011-10-01 12:00' and its fine. What's the contents of your edits? It could be wrong which would also explain a CAST error. Also what exactly do you mean by "It does not seem to work"? Roy Lambert [Team Elevate] |
Wed, Dec 21 2011 3:59 PM | Permanent Link |
John Postnikoff | Roy,
I never had any problems using the EDB Manager and used the time stamps as I have below. There must be something in the string that is not converting, I even tried to add a space and use other formats. I change my date separator from / to - and did have to make sure the date range was a valid date without zeros in the months etc. Here is my code for review again. This should be very simple. I do not know what I am missing that is not showing results. I am to a point that I do no longer get any errors, just no results on the table. To review here is what I have. Must be simple that I cannot see. //used when adding calendar date and time from pickers Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker11.time); Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker12.time); // contents of edits. // result for edit box 1 2000-01-01 12:00:01 AM // result for edit box 2 2011-12-31 11:59:59 PM // case 1 : DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > TIMESTAMP' + QuotedStr(Edit1.Text) + 'and ORDERDATE < TIMESTAMP'+ QuotedStr(Edit2.Text) + 'and CUSTCODE =' + QuotedStr(Edit3.Text)); John Postnikoff Roy Lambert wrote: John EDBManager is your friend. I just tested SELECT _HideAlarm FROM Calls WHERE _HideAlarm > TIMESTAMP '2011-01-01 12:00' AND _HideAlarm < TIMESTAMP '2011-10-01 12:00' and its fine. What's the contents of your edits? It could be wrong which would also explain a CAST error. Also what exactly do you mean by "It does not seem to work"? Roy Lambert [Team Elevate] |
Wed, Dec 21 2011 7:09 PM | Permanent Link |
John Postnikoff | I have solved the problem. To make a long story short. The culprit was the use of the date separator in Windows regional settings. When I realized it was still using yyyy/mm/dd instead of yyyy-mm-dd on my development machine and compile application all worked as it was supposed to. John John Postnikoff wrote: Under DBISAM I used the below code to extract data between two date-time stamp fields. This does not work under ElevateDB. I am wondering how I can make it work. I am not too familiar with casting and have had trouble trying other methods. I did double check my regional setting for the following format. yyyy-mm-dd hh:mm:ss AM . I noticed with that the precision time-stamp has four millisecond digits where there may have to be further adjustment, and which I would also like to use. //used when adding calendar date picker with DBISAM date-time stamp fields with seconds and AM or PM. Edit1.text := DatetoStr(DateTimePicker9.date)+ ' ' + TimetoStr(DateTimePicker1.time); Edit2.text := DatetoStr(DateTimePicker10.date)+ ' ' + TimetoStr(DateTimePicker2.time); // SQL between my DBISAM date-time stamps - works nice DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE >' + QuotedStr(Edit1.Text) + 'and orderdate <' + QuotedStr(Edit2.Text) + 'and custnumber =' + QuotedStr(Edit3.Text)) ; Any help is appreciated to get the above working with ElevateDB. John Postnikoff. |
Thu, Dec 22 2011 12:12 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Adam,
Just a minor nit: << Dates are no longer referenced as strings in EDB. >> This isn't exactly true. You still reference them like strings, but you need to preface them with the appropriate DATE, TIME, or TIMESTAMP keyword: DataMod.ReportQuery.SQL.Add('Select * From Shippers WHERE ORDERDATE > DATE ' + Engine.QuotedSQLStr(Edit1.Text) + 'and orderdate < DATE ' + Engine.QuotedSQLStr(Edit2.Text) + 'and custnumber =' + Engine.QuotedSQLStr(Edit3.Text)) ; That should do the trick. -- Tim Young Elevate Software www.elevatesoft.com |
Sat, Dec 31 2011 11:07 AM | Permanent Link |
Adam Brett Orixa Systems | Duly noted Tim, thanks. Never really clocked using the "TIMESTAMP" keyword before.
|
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 |