(SOLVED) Sqlite and date range query
lazarus / 16-Databases

----------------------------------------------------------------------
WibblyTim · 2017-01-23 15:09

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i var m, y:
integer;  endDate: TDateTime;  myYear, myMonth, myDay: word;  sd, ed:
string;  year, month: string;begin    year := IntToStr(y);  month :=
IntToStr(m);  if Length(month) < 2 then    month := '0' + month;  sd
:= year + '-' + month + '-01';   DecodeDate(endDate, myYear, myMonth,
myDay);  ed := year + '-' + month + '-' + IntToStr(myDay);  
SQLQuery.Close;  SQLQuery.SQL.Text := 'SELECT * FROM entry WHERE
strftime("%Y-%m-%d", date) BETWEEN "' + sd + '" AND "' + ed + '"'; 
SQLTransaction.Active := True;  SQLQuery.Open;end;         
This now returns all of a calendar months records in my Sqlite
database selected on their date. Phew!

----------------------------------------------------------------------
paweld · 2017-01-23 16:32

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 var m, y:
integer;  endDate: TDateTime;begin     SQLQuery.Close; 
SQLQuery.SQL.Text := 'SELECT * FROM entry WHERE date BETWEEN :startd
and :endd '; 
SQLQuery.ParamByName('startd').AsDateTime:=StartOfAMonth(y, m); 
SQLQuery.ParamByName('endd').AsDateTime:=endDate; 
SQLTransaction.Active := True;  SQLQuery.Open;end;

----------------------------------------------------------------------
WibblyTim · 2017-01-24 10:49

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.