Getting results from sql statement with between two dates

[Zvoni]

SQL 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]

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]

OK,  i can't see anything wrong.

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

[CraigC]

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]

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]

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]

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

[Zvoni]

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

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]

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