Sqlite and date range query
lazarus / 16-Databases

----------------------------------------------------------------------
WibblyTim · 2017-01-20 18:11

Hi all,

I'm using the SQLdb components.

I'm having a problem with a query, I cannot get it to work in the way
that I think it should!
I'm attempting to display records in a DBGrid that have a date of a
calendar month (January, February etc) but, I think I'm missing
something and I don't know what. I'm hoping a fresh pair of eyes will
be able to highlight my error.

Code here,
m := month as integer, y := year as integer;
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  endDate :=
EndOfAMonth(y, m);  startDate := EncodeDate(y, m, 1); 
 DecodeDate(startDate, myYear, myMonth, myDay);   sd := IntToStr(y) +
'-' + IntToStr(myMonth) + '-' + IntToStr(myDay);   DecodeDate(endDate,
myYear, myMonth, myDay);   ed := IntToStr(y) + '-' + IntToStr(myMonth)
+ '-' + IntToStr(myDay); ShowMessage(sd); ShowMessage(ed); SQLQuery.Cl
ose; SQLQuery.SQL.Text:='SELECT * FROM entry WHERE date BETWEEN ' + sd
+ ' AND ' + ed; SQLTransaction.Active := True;  SQLQuery.Open;       
          
The result of this should display only records that have dates within
a calendar month. If I substitute "2017-1-31" instead of ed for
example for January then the results are correct.

Can someone help me please?

----------------------------------------------------------------------
goodname · 2017-01-20 18:16

My guess would be the dates are not actually in quotes when you build
the query. I suggest using prepared / paramater queries to avoid the
need for formatting.

----------------------------------------------------------------------
LacaK · 2017-01-21 07:36

Right.
Either use:
  sd := QuotedStr(IntToStr(y) + '-' + IntToStr(myMonth) + '-' +
IntToStr(myDay));
or
  SQLQuery.SQL.Text:='SELECT * FROM entry WHERE date BETWEEN :sd AND
:ed';
  SQLQuery.ParamByName('sd').AsDate := startDate;
  SQLQuery.ParamByName('ed').AsDate := endDate;

----------------------------------------------------------------------
WibblyTim · 2017-01-22 17:33

goodname and Lacak, many thanks for your replies to my cry for help.
Unfortunately, despite my attempting every variation of code I can
devise I cannot seem to crack this problem. Most frustrating!

I have altered the record structure to post the date as a string
instead of a Date  but this also doesn't work.

I will have to have a think and come at the problem from another
angle.

Once again, thank you for your responses.