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;

Don't concat the SQL string yourself with variable data. Instead use SQL parameters for this! — DelphiCoder
– DelphiCoder, Commented Aug 23 at 15:07
On top of that, you have no idea what the string 07/01/2025 means - is that January 7th? Or July 1st? You only have assumptions. While SQLite has no real date type, if dates are stored as strings, they MUST be in ISO8601 format, ie YYYY-MM-DD. This will never match the ambiguous localized string 07/01/2025. Store dates properly and use actual date-typed parameters. SQLite will handle the rest — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Aug 23 at 15:21
@PanagiotisKanavos So the solution is to define two TEXT parameters to store the start/end dates and make the WHERE portion use the SQLite DATE() with the parameters? — Skypilot65
– Skypilot65, Commented Aug 23 at 16:07

Remy Lebeau · Accepted Answer · 2025-08-23 18:00:40Z

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;

Collectives™ on Stack Overflow

Unexpected SQLite SELECT statement results within Delphi

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related