(SOLVED) Sqlite and date range query

[WibblyTim]

Well, there we are, I've managed to solve the problem I had.

The code is this, where m := month as an integer (1..12) and y := year (2017...) as an integer.

Code: Pascal  [Select][+][-]

1. var
2.  m, y: integer;
3.   endDate: TDateTime;
4.   myYear, myMonth, myDay: word;
5.   sd, ed: string;
6.   year, month: string;
7. begin  
8.   year := IntToStr(y);
9.   month := IntToStr(m);
10.   if Length(month) < 2 then
11.     month := '0' + month;
12.   sd := year + '-' + month + '-01';
13.  
14.   DecodeDate(endDate, myYear, myMonth, myDay);
15.   ed := year + '-' + month + '-' + IntToStr(myDay);
16.  
17.   SQLQuery.Close;
18.   SQLQuery.SQL.Text := 'SELECT * FROM entry WHERE strftime("%Y-%m-%d", date) BETWEEN "' + sd + '" AND "' + ed + '"';
19.   SQLTransaction.Active := True;
20.   SQLQuery.Open;
21. end;
22.          

This now returns all of a calendar months records in my Sqlite database selected on their date. Phew!

[paweld]

Code: Pascal  [Select][+][-]

1. var
2.  m, y: integer;
3.   endDate: TDateTime;
4. begin  
5.   SQLQuery.Close;
6.   SQLQuery.SQL.Text := 'SELECT * FROM entry WHERE date BETWEEN :startd and :endd ';
7.   SQLQuery.ParamByName('startd').AsDateTime:=StartOfAMonth(y, m);
8.   SQLQuery.ParamByName('endd').AsDateTime:=endDate;
9.   SQLTransaction.Active := True;
10.   SQLQuery.Open;
11. end;

[WibblyTim]

I just knew that there would be a simpler and more elegant solution!

I have tidied up the code I submitted but your solution is even more compact.

Thanks paweld.