Recent

Author Topic: (SOLVED) Sqlite and date range query  (Read 2840 times)

WibblyTim

  • New Member
  • *
  • Posts: 46
    • http://www.wibblytim.co.uk
(SOLVED) Sqlite and date range query
« on: January 23, 2017, 04:09:15 pm »
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

  • Full Member
  • ***
  • Posts: 211
Re: (SOLVED) Sqlite and date range query
« Reply #1 on: January 23, 2017, 05:32:28 pm »
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;
Best regards
paweld

WibblyTim

  • New Member
  • *
  • Posts: 46
    • http://www.wibblytim.co.uk
Re: (SOLVED) Sqlite and date range query
« Reply #2 on: January 24, 2017, 11:49:53 am »
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.

 

TinyPortal © 2005-2018