SQLite component documentation
See also:
How To Open a SQLite connection and use it
SQLite and dates
SQLite explicitly says that it does not support any date/datetime SQL datatype.
So you have to store date as strings or numbers, and handle the comparison between dates using special SQLite functions.
The Gambas SQlite driver decided to store date/time as strings.
For example, to make the
BETWEEN comparison works, you must compare the date fields with values generated by the Gambas component, otherwise the comparison could only work by luck.
So, the request must be written that way:
Connection.Exec("SELECT * FROM clients WHERE fecha BETWEEN &1 AND &2", Date.FromUTC(Date(2025,05,14)), Date.FromUTC(Date(2025,05,15))
to get the following request:
SELECT * FROM clients WHERE fecha BETWEEN '2025-05-14T000000.000Z' AND '2025-05-15T000000.000Z'
Beware that the format changed between
gb.db and
gb.db2.
You will get another result with
gb.db that does not store the date the same way.
Another solution is not using Gambas Sqlite date fields, but numeric fields, where you store date/time as a numeric timestamp. For example the number of second since the UNIX Epoch.
Then the field is not directly readable, but you are sure that comparisons will always work.