Getting results from sql statement with between two dates
lazarus / 16-Databases

----------------------------------------------------------------------
CraigC · 2026-03-14 13:25

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 · 2026-03-14 13:31

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

----------------------------------------------------------------------
eny · 2026-03-14 13:59

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 · 2026-03-14 14:29

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 · 2026-03-14 14:44

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

----------------------------------------------------------------------
paweld · 2026-03-14 17:53

1. use CODE tags
2. If the DateCreated field is of type date, then the following query
should be correct
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 with
qryProject_ do  begin    Active := False;    SQL.Clear;    SQL.Add(
'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 :sEnd
');    SQL.Add(' ORDER BY TODO.DateCreated ');   
Params.ParamByName('sSec').AsString := qrySection_Section.AsString;  
Params.ParamByName('sStart').AsDate := dtpStart.Date;   
Params.ParamByName('sEnd').AsDate := dtpEnd.Date;    Active := True; 
End;End;

----------------------------------------------------------------------
CraigC · 2026-03-14 19:15

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

----------------------------------------------------------------------
paweld · 2026-03-15 05:05

The following code should work correctly:
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     with
qryProject_ do      begin        Active := False;        SQL.Clear;  
    SQL.Add( '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 :sEnd ');        SQL.Add('
ORDER BY TODO.DateCreated ');       
Params.ParamByName('sSec').AsString := qrySection_Section.AsString;  
    SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-
mm-dd', dtpStart.Date);        SQLQuery1.ParamByName('sEnd').AsString
:= FormatDateTime('yyyy-mm-dd', dtpEnd.Date);           Active :=
True;      End;    End;The example I used for testing:
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 procedure
TForm1.FormCreate(Sender: TObject);begin  Memo1.Lines.Clear; 
DBConnect;  //all rows  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(
'SELECT count(1) FROM TODO ');  SQLQuery1.Open;  Memo1.Lines.Add('all
rows count: ' + SQLQuery1.Fields[0].AsString);  SQLQuery1.Close; 
//section A  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add( 'SELECT count(1)
FROM TODO where SECTION = :sSec '); 
SQLQuery1.Params.ParamByName('sSec').AsString := 'A'; 
SQLQuery1.Open;  Memo1.Lines.Add('section A rows count: ' +
SQLQuery1.Fields[0].AsString);  SQLQuery1.Close;  //date between
2025-02-01 and 2025-02-28  SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add(
'SELECT count(1) FROM TODO where DateCreated BETWEEN :sStart AND :sEnd
');  SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-
mm-dd', EncodeDate(2025, 2, 1)); 
SQLQuery1.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd',
EncodeDate(2025, 2, 28));  SQLQuery1.Open;  Memo1.Lines.Add('february
2025 rows count: ' + SQLQuery1.Fields[0].AsString);  SQLQuery1.Close; 
//date between 2025-02-01 and 2025-02-28 and section A 
SQLQuery1.SQL.Clear;  SQLQuery1.SQL.Add( 'SELECT count(1) FROM TODO
where SECTION = :sSec and DateCreated BETWEEN :sStart AND :sEnd '); 
SQLQuery1.Params.ParamByName('sSec').AsString := 'A'; 
SQLQuery1.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-
dd', EncodeDate(2025, 2, 1));  SQLQuery1.ParamByName('sEnd').AsString
:= FormatDateTime('yyyy-mm-dd', EncodeDate(2025, 2, 28)); 
SQLQuery1.Open;  Memo1.Lines.Add('section A in february 2025 rows
count: ' + SQLQuery1.Fields[0].AsString); 
SQLQuery1.Close;end; procedure TForm1.DBConnect;const  sarr: array
[0..4] of String = ('A', 'B', 'C', 'D', 'E');var  i: Integer;begin 
SQLite3Connection1.Transaction := SQLTransaction1;  SQLQuery1.DataBase
:= SQLite3Connection1;  SQLQuery1.Transaction := SQLTransaction1; 
SQLite3Connection1.DatabaseName := 'test20260315.db'; 
SQLite3Connection1.Connected := True; 
SQLTransaction1.StartTransaction;  SQLQuery1.SQL.Text := ' create
table if not exists ToDo ( ' +    ' UID           Integer NOT NULL,  
    ' +    ' Subject       Char(20),                ' +    ' Section 
    Char(10),                ' +    ' FileName      Char(20),        
      ' +    ' DateCreated   Char(10) DEFAULT NULL,   ' +    '
DateCompleted Char(10) DEFAULT NULL,   ' +    ' CONSTRAINT PK_ToDo
PRIMARY KEY (UID)); ';  SQLQuery1.ExecSQL;  SQLQuery1.SQL.Text := '
select count(1) from ToDo ';  SQLQuery1.Open;  i :=
SQLQuery1.Fields[0].AsInteger;  SQLQuery1.Close;    
SQLTransaction1.Commit;  if i = 0 then  begin   
SQLTransaction1.StartTransaction;    SQLQuery1.SQL.Text := ' insert
into ToDO (UID, Subject, Section, FileName, DateCreated) values (:uid,
:sub, :sec, :fil, :dat) ';    for i := 1 to 365 do    begin     
SQLQuery1.ParamByName('uid').AsInteger := i;     
SQLQuery1.ParamByName('sub').AsString := 'Subject no ' + IntToStr(i); 
    SQLQuery1.ParamByName('sec').AsString := sarr[i mod 5];     
SQLQuery1.ParamByName('fil').AsString := 'text file ' + IntToStr(i) +
'.dat';      SQLQuery1.ParamByName('dat').AsString :=
FormatDateTime('yyyy-mm-dd', IncDay(EncodeDate(2024, 12, 31), i));   
 SQLQuery1.ExecSQL;    end;    SQLTransaction1.Commit;  end; 
SQLTransaction1.Active := True;end;

----------------------------------------------------------------------
gsa · 2026-03-15 08:52

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

Quote from: CraigC on March 14, 2026, 02:25:06 pm    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 · 2026-03-15 10:42

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 · 2026-03-15 11:41

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 · 2026-03-15 12:13

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 · 2026-03-15 17:27

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 · 2026-03-15 18:16

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

----------------------------------------------------------------------
paweld · 2026-03-15 19:54

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.

----------------------------------------------------------------------
CraigC · 2026-03-15 20:32

Well, I am now down to "LazEditTextDridPainter" used by
SynGutterBase.  SynEdit is installed as far as I can tell.

Thank you for taking the time to put this project together.

----------------------------------------------------------------------
paweld · 2026-03-15 21:22

The version without Synedit is attached.

----------------------------------------------------------------------
CraigC · 2026-03-15 23:17

It works.   But, I don't understand how it works because I have
formatted the dates the same way.  yyyy-mm-dd  and mm-dd-yyyy neither
one worked.

I'm going to step through this tomorrow and see what is done
differently.  Thank you for your time.

Craig

----------------------------------------------------------------------
Zvoni · 2026-03-16 07:36

Quote from: CraigC on March 14, 2026, 02:25:06 pmCode: Pascal
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i   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; Code: SQL
 [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i 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)Right, as already said in your other thread (and mentioned by
gsa)
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 case
rgFilterOptions.ItemIndex of      0 : SQL.Add('TODO.DateCompleted <>
null ');      1 : SQL.Add('TODO.DateCompleted = NULL ');You cannot
compare/filter directly on NULL. Period!
This never returns records.
gsa showed the correct syntax

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
SQL.Add('AND TODO.DateCreated BETWEEN :sStart AND TODO.DateCreated <=
:sEnd');This is wrong. You're looking for DateCreated between :sStart
and a Boolean, because SQL interprets this as a Boolean:
TODO.DateCreated <= :sEnd
pawel already showed you the correct syntax.
I'm more surprised this doesn't throw a syntax-error

I'm using Date-Fields in SQLite extensively, and never had a problem,
provided you USE the correct format in the Database itself, which
should always be "YYYY-MM-DD" as Text/Char and nothing else.

On a sidenote: If you use the BETWEEN-Operator in your WHERE-Clause,
the LEFT Parameter MUST be lower/equal than the RIGHT Parameter
A ".... MyDate BETWEEN '2026-06-16' AND '2026-03-13' will never return
records, even if you have records falling into that time-window

EDIT: hmmm...maybe this is actually what happened:
QuoteI'm more surprised this doesn't throw a syntax-errorThe right-
hand-side of the BETWEEN is evaluated as Boolean, which returns "0" or
"1" as an Integer.
The left-hand-side receives a valid value for sStart, which is >0/1,
or worse: left hand side is a String, right hand-side is a
boolean/Integer
When we account for implicit casting of right-hand-side from
boolean/Integer to String...
and then we would have the above scenario:
left-parameter Greater than right parameter --> No results

EDIT2: Remember: BETWEEN is inclusive of the boundaries.
If you need even one boundary excluded, you can't use BETWEEN, but you
need to write it out

----------------------------------------------------------------------
CraigC · 2026-03-16 08:40

Thank you for the reply.  I have tried so many different variations
it's not funny.    The date format, I tried both. I'm sure my changing
the SQL statement so many times probably got me in trouble along the
way. 

Thank you both for your time and expertise.

----------------------------------------------------------------------
Zvoni · 2026-03-16 09:26

Quote from: CraigC on March 16, 2026, 09:40:31 am
Thank you for the reply.  I have tried so many different variations
it's not funny.    The date format, I tried both. I'm sure my changing
the SQL statement so many times probably got me in trouble along the
way. 

Thank you both for your time and expertise.
There are 3 ways to store dates in SQLite
1) TEXT in ISO8601-Format
2) JulianDay --> Real (Float/Double)
3) Unixepoch (Integer)

After many try-and-error i've come to the conclusion, that the TEXT-
Format is the "best" way regarding the ratio of "upside/downside"
and definitely no mixing of Formats:
ISO8601-Format. Period!
"YYYY-MM-DD" (or "YYYY-MM-DD HH:NN:SS.ZZZ" if you additionally need
the time of day)
and nothing else

----------------------------------------------------------------------
CraigC · 2026-03-16 09:41

The date is being stored as "mm-dd-yyyy" in a date field.  So, it
looks like I really need a text field and store the date in "yyyy-mm-
dd" ?

----------------------------------------------------------------------
cdbc · 2026-03-16 09:47

Hi
QuoteSo, it looks like I really need a text field and store the date
in "yyyy-mm-dd" ? YES!
Regards Benny

----------------------------------------------------------------------
Zvoni · 2026-03-16 10:25

Quote from: CraigC on March 16, 2026, 10:41:02 am

The date is being stored as "mm-dd-yyyy" in a date field.  So, it
looks like I really need a text field and store the date in "yyyy-mm-
dd" ?
Don't use "Date" as DataType for a Column. It get's numeric affinity
Use CHAR/VARCHAR/TEXT (as shown in your first post), and store the
dates as "YYYY-MM-DD" and nothing else

Example
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i -- all
Dates in Format "mm-dd-yyyy"WITH        V       AS (SELECT
'08-18-2024' AS FromDate, '02-26-2026' AS ToDate),        X       AS
(SELECT '06-16-2025' AS MyDate)        SELECT * FROM X INNER JOIN V ON
1=1 WHERE MyDate BETWEEN FromDate AND ToDateReturns no record, despite
"June, 16th 2025" being between the 2 Dates

OTOH
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i -- all
dates in Format "yyyy-mm-dd"WITH        V       AS (SELECT
'2024-08-18' AS FromDate, '2026-02-26' AS ToDate),        X       AS
(SELECT '2025-06-16' AS MyDate)        SELECT * FROM X INNER JOIN V ON
1=1 WHERE MyDate BETWEEN FromDate AND ToDateReturns the correct record

----------------------------------------------------------------------
CraigC · 2026-03-16 10:36

Looks like my region defaults to "mm-dd-yyyy"  and if I change display
format and DateOrder it still wants to save as "mm-dd-yyyy"  I did a
showmessage before post event.  So I should use a parameter for INSERT
and UPDATE SQL statements? I tried adding a @s to the fieldname.  that
doesn't work.

It's getting more complicated by the moment.  Not even sure what event
to add the setting of the params.

procedure TfrmProject.qryProject_BeforeOpen(Dataset: Tdataset);
begin
  with qryProject_ do
  begin
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details,
DateCompleted, FileName '
      + ' FROM TODO Order BY DateCreated ';
    InsertSQL.Text :=
      'INSERT INTO ToDo(DateCreated, Subject, Section, Details,
DateCompleted, FileName) ' +
      'VALUES(:@sDateCreated, :Subject, :Section, :Details,
:@sDateCompleted, :FileName)';
    UpdateSQL.Text :=
      'UPDATE ToDo SET DateCreated=:@sDateCreated, Subject=:Subject,
Section=:Section, ' +
      'Details=:Details, DateCompleted=:@sDateCompleted,
FileName=:FileName WHERE UID=:OLD_UID';
  end;
end;

Procedure Tfrmproject.Qryproject_beforepost(Dataset : Tdataset);
Begin
  qryProject_.Params.ParamByName('@sDateCreated').AsString :=
FormatDateTime('yyyy-mm-dd', Qryproject_datecreated.AsDateTime);
  if not Qryproject_datecompleted.IsNull then
  qryProject_.Params.ParamByName('@sDateCompleted').AsString :=
FormatDateTime('yyyy-mm-dd', Qryproject_datecompleted.AsDateTime);
End;

----------------------------------------------------------------------
Zvoni · 2026-03-16 10:51

Remove the "@" from the Parameter-Names.
It's either a colon ":", or it's the "@", never both.

And you should remember, there is a difference, how something is
displayed (--> Locales!), and how something is stored.

Write a simple routine taking a TDateTime (e.g. from a Datepicker),
and fire an INSERT against your Database, using the correct Formatting
(as shown in your sample above).
and then look in the Database itself, if it's stored correctly,
because SQLite is locale-agnostic regarding Date-Formats

EDIT: Just noticed: You are using InsertSQL and UpdateSQL
Parameternames MUST be the Columnname prepended with a colon ":"
Remove the "@" and "s"
Quote UpdateSQL.Text :=
      'UPDATE ToDo SET DateCreated=:@sDateCreated, Subject=:Subject,
Section=:Section, ' +
      'Details=:Details, DateCompleted=:@sDateCompleted,
FileName=:FileName WHERE UID=:OLD_UID';

----------------------------------------------------------------------
CraigC · 2026-03-16 16:41

I have the date being saved correctly. See screenshot. But the filter
for only the selected section and dates, does not work, (again) I
don't see a reason why.

 with qryProject_ do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details,
DateCompleted, FileName '
      + ' FROM TODO WHERE TODO.SECTION = :sSec AND DateCreated BETWEEN
:sStart AND :sEnd ' +
      ' 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);
    mSQL.Clear;
    mSQL.Lines.Add(qrySection_Section.AsString);
    mSQL.Lines.Add(params.ParamByName('sStart').AsString);
    mSQL.Lines.Add(params.ParamByName('sEnd').AsString);
    mSQL.Lines.Add(qryProject_.SQL.Text);
    Open;
  end;

----------------------------------------------------------------------
eny · 2026-03-16 18:42

I'm assuming that 'Completed' is your dtEnd.
As mentioned above, comparing any value with a NULL value will not
give any results.
This also applies to Between.

You have to handle the possible NULL values in dtEnd explicitly by,
for example, using the COALESCE function.
So change your query to something like below:
Code: [Select]SELECT UID, DateCreated, Subject, Section, Details,
DateCompleted, FileName
  FROM TODO
WHERE TODO.SECTION = :sSec
   AND DateCreated BETWEEN :sStart AND COALESCE(:sEnd, '9999-12-31')
 ORDER BY TODO.DateCreated

----------------------------------------------------------------------
CraigC · 2026-03-16 18:46

Completed is the date in which the task was completed.  It's not used
in the parameters. Just the DateCreated.

Completed will be null for the most part.

----------------------------------------------------------------------
eny · 2026-03-16 18:53

Can dtEnd be NULL?
Is it NULL in your (test) data?

----------------------------------------------------------------------
CraigC · 2026-03-16 19:00

The query only looks for a range of dates on the DateCreated field. 
 Start and End.

----------------------------------------------------------------------
eny · 2026-03-16 19:20

Quote from: CraigC on March 16, 2026, 08:00:29 pm
The query only looks for a range of dates on the DateCreated field. 
 Start and End.
My mistake. Long day...

Have you tried copying the query that is in your screen print and run
that directly in your query builder?
(Replacing dtStart and dtEnd with the shown date values.)

----------------------------------------------------------------------
CraigC · 2026-03-16 20:50

SELECT UID, DateCreated, Subject, Section, Details, DateCompleted,
FileName
     FROM TODO WHERE TODO.SECTION = 'AP' AND DateCreated BETWEEN
'2026-01-05' AND '2026-02-27'
     ORDER BY TODO.DateCreated

this works with the DB Browser for SQLite.  I get 4 records

----------------------------------------------------------------------
CraigC · 2026-03-16 21:00

In the Visual Query Builder I can use parameters and it worked as
well.  Dates are saved in the 'yyyy-mm-dd' format.  I thought for
sure, today it would work in Lazarus. The screenshot shows the SQL
statement in the memo.

----------------------------------------------------------------------
Zvoni · 2026-03-17 07:06

Show your code for Button "Refresh"
because i think that's the crucial one.

*sigh*
SQLite has an API called "ExpandedSQL", which would return the SQL-
Statement, after the Parameters have been set (a.k.a. what the
Statement really looks like on the DB-side)
but it's not implemented by freepascal.
I remember implementing it myself, but i had to hack deep into sqldb
and sqlite3conn

btw: What ARE your Parameters, and what is the expected result?
I can only guess it would be the record with ID 6

----------------------------------------------------------------------
CraigC · 2026-03-17 09:20

the code is trying to retrieve:

1.)  data for Section selected
2.) between two dates.

procedure Tfrmproject.Bbnrefreshclick(Sender: TObject);
begin
  with qryProject_ do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details,
DateCompleted, FileName '
      + ' FROM TODO WHERE TODO.SECTION = :sSec AND DateCreated BETWEEN
:sStart AND :sEnd ' +
      ' 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);
    Open;
  end;
end;

----------------------------------------------------------------------
Zvoni · 2026-03-17 09:36

hmmm.....looks good...
and you say it works in DB Browser?

As a Test:
hardcode the Values you're sending as Parameters, and report back
Note the double single-quotes for escaping
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 procedure
Tfrmproject.Bbnrefreshclick(Sender: TObject);begin  with qryProject_
do  begin    Active := False;    SQL.Clear;    SQL.Text := 'SELECT
UID, DateCreated, Subject, Section, Details, DateCompleted, FileName
'      + ' FROM TODO WHERE TODO.SECTION = ''OE'' AND DateCreated
BETWEEN ''2026-02-14'' AND ''2026-03-16'' ' +      ' 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);    Open;  end;end;
Possible Results:
1) No Result --> then there is something wrong with the Values in your
Columns (e.g. leading/trailing spaces)
2) You get the expected Result --> There is something wrong with the
Values of the 3 Parameters, and my money is on "qrySection_Section"

----------------------------------------------------------------------
CraigC · 2026-03-17 12:37

The SQL statement works in the DB Browser. Of course it does.   I'm
changing components to Zeos Access.  Once I get this connected I'll
try running it.

I used trim(Section) to see if that had any difference.  It did not. 
In the screenshot I attached to a previous post, the dates were
formatted correctly.  Even the saving of the date is correct in the
string field.

Stay tuned.

----------------------------------------------------------------------
CraigC · 2026-03-17 13:40

I gave up on getting Zeos Access to connect to my database.  Which is
okay.  I found the problem.

  Params.ParamByName('sSec').AsString :=
TrimRight(qrySection_Section.AsString);

Trim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.  You were right Zvoni!

Hopefully, I can make some progress now.   Thank you all for your
replies and time.

Craig

----------------------------------------------------------------------
Zvoni · 2026-03-17 14:49

Quote from: CraigC on March 17, 2026, 02:40:51 pm
I gave up on getting Zeos Access to connect to my database.  Which is
okay.  I found the problem.

  Params.ParamByName('sSec').AsString :=
TrimRight(qrySection_Section.AsString);

Trim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.  You were right Zvoni!

Hopefully, I can make some progress now.   Thank you all for your
replies and time.

Craig
nice that you solved it.

BUT:
QuoteTrim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.This is not making any sense.
Trim should definitely work.
Ah well....

----------------------------------------------------------------------
dsiders · 2026-03-17 15:41

Quote from: Zvoni on March 17, 2026, 03:49:12 pmQuote from: CraigC on
March 17, 2026, 02:40:51 pm
I gave up on getting Zeos Access to connect to my database.  Which is
okay.  I found the problem.

  Params.ParamByName('sSec').AsString :=
TrimRight(qrySection_Section.AsString);

Trim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.  You were right Zvoni!

Hopefully, I can make some progress now.   Thank you all for your
replies and time.

Craig
nice that you solved it.

BUT:
QuoteTrim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.This is not making any sense.
Trim should definitely work.
Ah well....

Unless the section data (in the db) actually starts with leading
whitespace.

----------------------------------------------------------------------
Zvoni · 2026-03-18 06:56

Quote from: dsiders on March 17, 2026, 04:41:44 pmQuote from: Zvoni on
March 17, 2026, 03:49:12 pmQuote from: CraigC on March 17, 2026,
02:40:51 pm
I gave up on getting Zeos Access to connect to my database.  Which is
okay.  I found the problem.

  Params.ParamByName('sSec').AsString :=
TrimRight(qrySection_Section.AsString);

Trim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.  You were right Zvoni!

Hopefully, I can make some progress now.   Thank you all for your
replies and time.

Craig
nice that you solved it.

BUT:
QuoteTrim() didn't work before, but I looked into what was available. 
TrimRight() did the trick.This is not making any sense.
Trim should definitely work.
Ah well....

Unless the section data (in the db) actually starts with leading
whitespace.
Now there is an idea....which coincides with my thoughts on the
possible results, specifically number 1).
and is easily solved with a BEFORE-INSERT Trigger (I always try to
sanitize on the backend instead from the frontend)

----------------------------------------------------------------------
CraigC · 2026-03-18 10:16

I have to be the bearer of bad news, but it stopped working.  In the
Visual Query Builder, I can set params.  It didn't work there either. 
I dropped the table and recreated with a SQL statement I had saved. 
Still not working.

Something else must be going on.

----------------------------------------------------------------------
Zvoni · 2026-03-18 10:59

Quote from: CraigC on March 18, 2026, 11:16:32 amI have to be the
bearer of bad news, but it stopped working.  In the Visual Query
Builder, I can set params.  It didn't work there either.  I dropped
the table and recreated with a SQL statement I had saved.  Still not
working.

Something else must be going on.
Make a SQL-Dump of the Data
The "Table as CSV" should be enough, i think.
Structure AND Data!

----------------------------------------------------------------------
jcmontherock · 2026-03-18 17:42

SQL is more usable for restoring the db.

----------------------------------------------------------------------
Zvoni · 2026-03-18 19:47

Quote from: jcmontherock on March 18, 2026, 06:42:47 pmSQL is more
usable for restoring the db.
It’s not about restoring.

I want to take a look at how his data is really stored.

As in: are there leading/trailing whitespaces that might cause the
comparison to fail

Nevermind i could just dump it into a database here and go on the hunt
for the error

----------------------------------------------------------------------
CraigC · 2026-03-18 20:02

CREATE TABLE IF NOT EXISTS "ToDo" (
   "UID"   Integer NOT NULL,
   "Subject"   Char(20),
   "Section"   Char(10),
   "Details"   BLOB,
   "FileName"   Char(20),
   "DateCreated"   Char(10),
   "DateCompleted"   Char(10) DEFAULT NULL,
   CONSTRAINT "PK_ToDo" PRIMARY KEY("UID")
);
INSERT INTO "UIDS" VALUES (100019);
INSERT INTO "ToDo" VALUES (1,'Function ','IP','New and delete option
removed for now.','IP__product.pas','2026-02-03',NULL);
INSERT INTO "ToDo" VALUES
(2,'testing','OE','testing',NULL,'2026-03-17',NULL);
INSERT INTO "ToDo" VALUES (3,'AR POSTING','AR','Need to update posting
of OE and then AR.',NULL,'2026-03-18',NULL);
INSERT INTO "ToDo" VALUES (4,'Tables','GL','GL tables have not been
setup. No code has been touched yet either.',NULL,'2026-03-18',NULL);
INSERT INTO "ToDo" VALUES (5,'More Tables','AP','AP will need tables
created.',NULL,'2026-03-18',NULL);
INSERT INTO "ToDo" VALUES (6,'Entering routine','GL','GUI hasn''t been
updated.',NULL,'2026-03-18',NULL);
CREATE INDEX IF NOT EXISTS "DateCreated" ON "ToDo" (
   "DateCreated"
);

----------------------------------------------------------------------
Zvoni · 2026-03-19 06:58

OK,  i can't see anything wrong.

and which SELECT doesn't work? as in: Which Parametersfor which
values?

----------------------------------------------------------------------
CraigC · 2026-03-19 10:24

The Section parameter is the one that stands out.  But the dates don't
seem to be falling in line with their parameters either.  So, I'd say
the entire statement doesn't work as it is setup.  I'm going to setup
a project with other components and see if that helps in any way. 
Also use variables to supply the parameters.  I'll get back here when
I get a result either way.

Thank you.

----------------------------------------------------------------------
Zvoni · 2026-03-19 11:23

On a SideNote: Why are you using BLOB for Column "Details"?
I only see Strings inserted there.
Is it planned for, say PDF's in the Future?
Otherwise it's not making sense

----------------------------------------------------------------------
CraigC · 2026-03-19 11:27

I have another project setup with Zeos Access components to retrieve
based on the parameters.  It works as designed.  Section selected is
returned along with the correct dates.  I even set the dates to excude
a record and it works within the parameters.  Same database is being
queried.  Same SQL statement is used.

?  I don't know what to think.  Up until now, the SQLdb components
have done what has been asked of them.

Craig

----------------------------------------------------------------------
CraigC · 2026-03-19 11:29

I set the "Details" field to Blob for longer explanations should I
want to get long winded.

----------------------------------------------------------------------
Zvoni · 2026-03-19 11:51

Quote from: CraigC on March 19, 2026, 12:29:11 pm
I set the "Details" field to Blob for longer explanations should I
want to get long winded. 
Which is utter nonsense to use BLOB, since BLOB and TEXT have the same
Limitation
https://www.sqlite.org/limits.html#max_length
Quote The maximum number of bytes in a string or BLOB in SQLite is
defined by the preprocessor macro SQLITE_MAX_LENGTH. The default value
of this macro is 1 billion (1 thousand million or 1,000,000,000). You
can raise or lower this value at compile-time using a command-line
option like this:

    -DSQLITE_MAX_LENGTH=123456789

The current implementation will only support a string or BLOB length
up to 231-3 or 2147483645. And some built-in functions such as hex()
might fail well before that point. In security-sensitive applications
it is best not to try to increase the maximum string and blob length.
In fact, you might do well to lower the maximum string and blob length
to something more in the range of a few million if that is possible.

During part of SQLite's INSERT and SELECT processing, the complete
content of each row in the database is encoded as a single BLOB. So
the SQLITE_MAX_LENGTH parameter also determines the maximum number of
bytes in a row.

The maximum string or BLOB length can be lowered at run-time using the
sqlite3_limit(db,SQLITE_LIMIT_LENGTH,size) interface. Bottom Line: If
"Details" is always Text/String, then USE the correct DataType (TEXT,
CHAR, VARCHAR)

I repeat: something like "... CHAR(20)" --> SQLite IGNORES the "(20)"

You can Create your Column like
.....
 Details CHAR(20),
.........
and STILL insert a String with a few 1000 Bytes/Characters

More concerning is your issue with sqldb.
That is, if i understood you correctly, that with ZEOS it works, with
sqldb it doesn't, provided the same Database and Statements

----------------------------------------------------------------------
CraigC · 2026-03-19 15:04

Okay, I'll ignore using Blob. It's what I used in the past.    Not
used to a DB disregarding their own guardrails.

>>More concerning is your issue with sqldb.
That is, if i understood you correctly, that with ZEOS it works, with
sqldb it doesn't, provided the same Database and Statements<<

Yeah, I'll be back if there is anything else to report.
Thanks,

Craig