Getting results from sql statement with between two dates

[CraigC]

I have tried everything I can think of and searched Google. even changed field types to char instead of Date.

Will not display data equal or between the dates.

  with qryProject_ do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := 'SELECT * FROM TODO WHERE ';
    case rgFilterOptions.ItemIndex of
      0 : SQL.Add('TODO.DateCompleted <> null ');
      1 : SQL.Add('TODO.DateCompleted = NULL ');
    end;
    SQL.Add('AND TODO.SECTION = :sSec ');
    SQL.Add('AND TODO.DateCreated BETWEEN :sStart AND TODO.DateCreated <= :sEnd');
    SQL.Add(' ORDER BY TODO.DateCreated');
    Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
    Params.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', dtpStart.Date);
    Params.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd',dtpEnd.Date);
    Active := True;
  End;
End;


CREATE TABLE ToDo
(
    UID           Integer NOT NULL,
    Subject       Char(20),
    Section       Char(10),
    Details       BLOB,
    FileName      Char(20),
    DateCreated   Char(10) DEFAULT NULL,
    DateCompleted Char(10) DEFAULT NULL,
    CONSTRAINT PK_ToDo PRIMARY KEY (UID)

[CraigC]

I already removed the <= sign.  I tried that before using "Between"

[eny]

Not a complete answer yet (not sure what DB you are using).
But I always first try to run the query directly in some DB viewer (e.g. SQLiteadmin for SQLite) to check the syntax and see what the query should return.
Then I copy/paste/translate to Lazarus.

[CraigC]

SQLite

I did that used an visual Query builder.  But I also read where you needed to format the date correctly.  So I am getting conflicting info.

 SELECT
  ToDo.DateCreated,
  ToDo.Subject,
  ToDo.Section,
  ToDo.Details
FROM
  ToDo
WHERE
  (ToDo.DateCreated >= 02 - 2 - 2026 AND
   ToDo.Section = 'AP') OR
  (ToDo.DateCreated <= 03 - 15 - 2026)

[CraigC]

It works in the Visual Query Builder.  But not in the app.

[paweld]

1. use CODE tags
2. If the DateCreated field is of type date, then the following query should be correct

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

1. with qryProject_ do
2.   begin
3.     Active := False;
4.     SQL.Clear;
5.     SQL.Add( 'SELECT * FROM TODO WHERE ');
6.     case rgFilterOptions.ItemIndex of
7.       0 : SQL.Add(' TODO.DateCompleted <> null ');
8.       1 : SQL.Add(' TODO.DateCompleted = NULL ');
9.     end;
10.     SQL.Add(' AND TODO.SECTION = :sSec ');
11.     SQL.Add(' AND TODO.DateCreated BETWEEN :sStart AND :sEnd ');
12.     SQL.Add(' ORDER BY TODO.DateCreated ');
13.     Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
14.     Params.ParamByName('sStart').AsDate := dtpStart.Date;
15.     Params.ParamByName('sEnd').AsDate := dtpEnd.Date;
16.     Active := True;
17.   End;
18. End;

[CraigC]

They were date type fields. I changed them to Char 10.

[paweld]

The following code should work correctly:

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

1.     with qryProject_ do
2.       begin
3.         Active := False;
4.         SQL.Clear;
5.         SQL.Add( 'SELECT * FROM TODO WHERE ');
6.         case rgFilterOptions.ItemIndex of
7.           0 : SQL.Add(' TODO.DateCompleted <> null ');
8.           1 : SQL.Add(' TODO.DateCompleted = NULL ');
9.         end;
10.         SQL.Add(' AND TODO.SECTION = :sSec ');
11.         SQL.Add(' AND TODO.DateCreated BETWEEN :sStart AND :sEnd ');
12.         SQL.Add(' ORDER BY TODO.DateCreated ');
13.         Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
14.         SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', dtpStart.Date);
15.         SQLQuery1.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd', dtpEnd.Date);  
16.         Active := True;
17.       End;
18.     End;

The example I used for testing:

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

1. procedure TForm1.FormCreate(Sender: TObject);
2. begin
3.   Memo1.Lines.Clear;
4.   DBConnect;
5.   //all rows
6.   SQLQuery1.SQL.Clear;
7.   SQLQuery1.SQL.Add( 'SELECT count(1) FROM TODO ');
8.   SQLQuery1.Open;
9.   Memo1.Lines.Add('all rows count: ' + SQLQuery1.Fields[0].AsString);
10.   SQLQuery1.Close;
11.   //section A
12.   SQLQuery1.SQL.Clear;
13.   SQLQuery1.SQL.Add( 'SELECT count(1) FROM TODO where SECTION = :sSec ');
14.   SQLQuery1.Params.ParamByName('sSec').AsString := 'A';
15.   SQLQuery1.Open;
16.   Memo1.Lines.Add('section A rows count: ' + SQLQuery1.Fields[0].AsString);
17.   SQLQuery1.Close;
18.   //date between 2025-02-01 and 2025-02-28
19.   SQLQuery1.SQL.Clear;
20.   SQLQuery1.SQL.Add( 'SELECT count(1) FROM TODO where DateCreated BETWEEN :sStart AND :sEnd ');
21.   SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', EncodeDate(2025, 2, 1));
22.   SQLQuery1.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd', EncodeDate(2025, 2, 28));
23.   SQLQuery1.Open;
24.   Memo1.Lines.Add('february 2025 rows count: ' + SQLQuery1.Fields[0].AsString);
25.   SQLQuery1.Close;
26.   //date between 2025-02-01 and 2025-02-28 and section A
27.   SQLQuery1.SQL.Clear;
28.   SQLQuery1.SQL.Add( 'SELECT count(1) FROM TODO where SECTION = :sSec and DateCreated BETWEEN :sStart AND :sEnd ');
29.   SQLQuery1.Params.ParamByName('sSec').AsString := 'A';
30.   SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', EncodeDate(2025, 2, 1));
31.   SQLQuery1.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd', EncodeDate(2025, 2, 28));
32.   SQLQuery1.Open;
33.   Memo1.Lines.Add('section A in february 2025 rows count: ' + SQLQuery1.Fields[0].AsString);
34.   SQLQuery1.Close;
35. end;
36.  
37. procedure TForm1.DBConnect;
38. const
39.   sarr: array [0..4] of String = ('A', 'B', 'C', 'D', 'E');
40. var
41.   i: Integer;
42. begin
43.   SQLite3Connection1.Transaction := SQLTransaction1;
44.   SQLQuery1.DataBase := SQLite3Connection1;
45.   SQLQuery1.Transaction := SQLTransaction1;
46.   SQLite3Connection1.DatabaseName := 'test20260315.db';
47.   SQLite3Connection1.Connected := True;
48.   SQLTransaction1.StartTransaction;
49.   SQLQuery1.SQL.Text := ' create table if not exists ToDo ( ' +
50.     ' UID           Integer NOT NULL,        ' +
51.     ' Subject       Char(20),                ' +
52.     ' Section       Char(10),                ' +
53.     ' FileName      Char(20),                ' +
54.     ' DateCreated   Char(10) DEFAULT NULL,   ' +
55.     ' DateCompleted Char(10) DEFAULT NULL,   ' +
56.     ' CONSTRAINT PK_ToDo PRIMARY KEY (UID)); ';
57.   SQLQuery1.ExecSQL;
58.   SQLQuery1.SQL.Text := ' select count(1) from ToDo ';
59.   SQLQuery1.Open;
60.   i := SQLQuery1.Fields[0].AsInteger;
61.   SQLQuery1.Close;  
62.   SQLTransaction1.Commit;
63.   if i = 0 then
64.   begin
65.     SQLTransaction1.StartTransaction;
66.     SQLQuery1.SQL.Text := ' insert into ToDO (UID, Subject, Section, FileName, DateCreated) values (:uid, :sub, :sec, :fil, :dat) ';
67.     for i := 1 to 365 do
68.     begin
69.       SQLQuery1.ParamByName('uid').AsInteger := i;
70.       SQLQuery1.ParamByName('sub').AsString := 'Subject no ' + IntToStr(i);
71.       SQLQuery1.ParamByName('sec').AsString := sarr[i mod 5];
72.       SQLQuery1.ParamByName('fil').AsString := 'text file ' + IntToStr(i) + '.dat';
73.       SQLQuery1.ParamByName('dat').AsString := FormatDateTime('yyyy-mm-dd', IncDay(EncodeDate(2024, 12, 31), i));
74.       SQLQuery1.ExecSQL;
75.     end;
76.     SQLTransaction1.Commit;
77.   end;
78.   SQLTransaction1.Active := True;
79. end;

[gsa]

n SQL NULL means undefined, unkown. So you can't compare NULL to anythhing.
Your SQL statement

    case rgFilterOptions.ItemIndex of
      0 : SQL.Add('TODO.DateCompleted <> null ');
      1 : SQL.Add('TODO.DateCompleted = NULL ');
    end;

should be changed to

    case rgFilterOptions.ItemIndex of
      0 : SQL.Add(' TODO.DateCompleted is not null ');
      1 : SQL.Add(' TODO.DateCompleted is NULL ');
    end;

[CraigC]

thanks for the replys.  As you can see I have gutted the SQL statement to nearly nothing.  It still will not work.  It works perfectly in the Database Workbench Visual Query builder (running via Wine) but not in  Lazarus running on Linux Mint.  Using SQLdb components.  I even removed the Parameter option.


  with qryProject_ do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details, DateCompleted, FileName '
      + ' FROM TODO WHERE TODO.SECTION = '+ qrySection_Section.AsString +' ORDER BY TODO.DateCreated ';
//    Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
    mSQL.Clear;  // to verify SQL
    mSQL.Lines.Add(qrySection_Section.AsString);
    mSQL.Lines.Add(qryProject_.SQL.Text);
    Open;
  end;                                       

[paweld]

It doesn't work because the strings in the query must be enclosed in single quotes, and it's best to use parameters instead of concatenating strings.
In my previous post, I provided a working example. If you can, please attach your test program; perhaps the problem lies elsewhere.

[CraigC]

I would prefer to use params.  And I did.  It's a dead simple SQL statement now after removing 3 field references.  it still doesn't work. 

I am starting to wonder about the db components. 

[paweld]

The attachment contains a simple project. Compile it, copy the SQLite library and your database (as name ‘test.db’) to the program directory, and check if it works.

[CraigC]

Error: Cannot open include file "synedit.inc"  It's in the package.  Any ideas?  I'm still fairly new in regards to Lazarus.

[paweld]

Sorry, but I always forget to mention this - I’m use the Lazarus trunk, so some components might not be compatible with the stable version. I’ve fixed those bugs, and now it works fine with the stable version of Lazarus—I tested it on version 4.4.
Please download the attachment from my previous post again.