Unexpected SQLite SELECT statement results within Delphi

Question

I am receiving unexpected results from an SQL SELECT statement in my Delphi 12.3 app. I believe the SQL statement is properly formatted, however, I get no records.

I tested the following SQL statements and they returned the appropriate results:

SELECT * FROM ExpGenItem;

SELECT * FROM ExpGenItem INDEXED BY idx_pdate;

When I executed the following SQL statement, I receive no results (I should get 5-6):

SELECT * 
FROM ExpGenItem INDEXED BY idx_pdate 
WHERE ExpDate BETWEEN '07/01/2025' AND '07/15/2025';

The ExpDate column in the SQL datebase is of type DATE, and the application code uses two TDateTimePicker controls to generate the start/end dates for the SQL statement.

Here is the code that builds the SQL statement and executes it. Where RepStateDate and RepEndDate are both TDateTime types. The function etStrQuote() simply appends the single quote mark (') to the date strings.

sqltxt:='SELECT * FROM ExpGenItem INDEXED BY idx_pdate WHERE ';
sqltxt:=sqltxt+'ExpDate BETWEEN '+etStrQuote(DateToStr(RepStartDate))+' ';
sqltxt:=sqltxt+'AND '+etStrQuote(DateToStr(RepEndDate))+';';

dmData1.ExpItemQuery.SQL.Text:=sqltxt;
dmData1.ExpItemQuery.Open;

Answer 1

SQLite requires you to specify date strings in ISO-8601 format. The DateToStr() function does not support that format, but there is a DateToISO8601() function available (and QuotedStr(), too), eg:

uses
  ..., DateUtils, SysUtils;

sqltxt := 'SELECT * FROM ExpGenItem INDEXED BY idx_pdate WHERE ';
sqltxt := sqltxt + 'ExpDate BETWEEN ' + QuoteStr(DateToISO8601(RepStartDate)) + ' ';
sqltxt := sqltxt + 'AND ' + QuoteStr(DateToISO8601(RepEndDate)) + ';';

Or, you can use FormatDateTime() instead:

uses
  ..., SysUtils;

const
  FormatStr = '"'"yyyy"-"mm"-"dd"'"';

sqltxt := 'SELECT * FROM ExpGenItem INDEXED BY idx_pdate WHERE ';
sqltxt := sqltxt + 'ExpDate BETWEEN ' + FormatDateTime(FormatStr,RepStartDate) + ' ';
sqltxt := sqltxt + 'AND ' + FormatDateTime(FormatStr,RepEndDate) + ';';

However, that being said, you SHOULD NOT be concatenating your SQL queriy manually at all. Use a parameterized query instead. Let the database engine handle the proper formatting for you. For example:

dmData1.ExpItemQuery.SQL.Text := 'SELECT * FROM ExpGenItem INDEXED BY idx_pdate WHERE ExpDate BETWEEN :StartDate AND :EndDate';
dmData1.ExpItemQuery.ParamByName('StartDate').AsDate := RepStartDate;
dmData1.ExpItemQuery.ParamByName('EndDate').AsDate := RepEndDate;
dmData1.ExpItemQuery.Open;