Getting results from sql statement with between two dates

[CraigC]

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

[eny]

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]

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]

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]

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]

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]

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][+][-]

1. procedure Tfrmproject.Bbnrefreshclick(Sender: TObject);
2. begin
3.   with qryProject_ do
4.   begin
5.     Active := False;
6.     SQL.Clear;
7.     SQL.Text := 'SELECT UID, DateCreated, Subject, Section, Details, DateCompleted, FileName '
8.       + ' FROM TODO WHERE TODO.SECTION = ''OE'' AND DateCreated BETWEEN ''2026-02-14'' AND ''2026-03-16'' ' +
9.       ' ORDER BY TODO.DateCreated ';
10.     //Params.ParamByName('sSec').AsString := qrySection_Section.AsString;
11.     //Params.ParamByName('sStart').AsString := FormatDateTime('yyyy-mm-dd', dtpStart.Date);
12.     //Params.ParamByName('sEnd').AsString := FormatDateTime('yyyy-mm-dd', dtpEnd.Date);
13.     Open;
14.   end;
15. 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]

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]

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]

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:

Trim() 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]

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:

Trim() 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]

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:

Trim() 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]

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]

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.

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

[jcmontherock]

SQL is more usable for restoring the db.