### Bookstore

 Computer Math and Games in Pascal (preview) Lazarus Handbook

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

#### WibblyTim

• New Member
• Posts: 46
##### (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
##### 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.