[Gambas-bugtracker] Bug #3048: gb.db2 Type mismatch: wanted Date, got String instead.

[<bugtracker@xxxxxxxxxxxxxx>]

http://gambaswiki.org/bugtracker/edit?object=BUG.3048&from=L21haW4-

Comment #4 by Benoît MINISINI:

Here is a new comment about the sqlite date problem.

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, and the format changed between 'gb.db' and 'gb.db2'.

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, you have to write your request 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'

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 field, but numeric field, where you store 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.


----[ Gambas bugtracker-list is hosted by https://www.hostsharing.net ]----