[Gambas-bugtracker] Bug #2239: Connection. connection form: SQL requests panel malfunction with date

[bugtracker at gambaswiki.org]

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

Comment #19 by Benoît MINISINI:

It's because SQLite stores date as strings.

Gambas chose to store dates with the following format: "YYYY-MM-DD HH:MM:SS".

But the date() SQLite operator only returns dates as "YYYY-MM-DD", so the comparison operator fails.

You must write your request that way:

/* Delivery tomorrow */
SELECT tcust.cunam as 'NAME', tcont.costa AS 'START OF RENTAL', tcont.coend AS 'END OF RENTAL'
FROM tcont, tcust
WHERE tcont.cukey=tcust.cukey
AND tcont.costa=date('2021-08-01', '+1 day') || ' 00:00:00';

Apparently "||" is the string concatenation operator in SQLite.

Or you should better use Gambas to manage the values for you:

MyConn.Exec("SELECT tcust.cunam as 'NAME', tcont.costa AS 'START OF RENTAL', tcont.coend AS 'END OF RENTAL' FROM tcont, tcust WHERE tcont.cukey = tcust.cukey
AND tcont.costa = &1", DateAdd(Date(2021,08,01), gb.Day, 1))