As far as I can tell, dates in SQLite are held pretty much as text.
In my db, thats 20160312 style text
I have 2 tables
Struct which holds a reference to a table People and an ‘OccupancyDate’
People which holds people and their StartDate
I want to see if a person is in the Struct table with an OccupancyDate after their Start Date.
This SQL:
Select Struct.OccupantRef, Struct.OccupancyDate , People.StartDate from Struct , People where Struct.OccupantRef = People.PER_REF_NO
returns rows.
This SQL:
Select Struct.OccupantRef, Struct.OccupancyDate , People.StartDate from Struct , People where Struct.OccupantRef = People.PER_REF_NO and Struct.OccupancyDate > People.StartDate
returns nothing.
But I know there are 3 records that should show up, and the actual database using this exact SQL shows me the 3 rows when I use
https://sqliteonline.com/
to query the data.
Why would my app work differently to the online browser in this regard?
The code:
[code] rs = thedb.sqlselect (“Select Struct.OccupantRef, Struct.OccupancyDate , People.StartDate from Struct , People where Struct.OccupantRef = People.PER_REF_NO”)
//and Struct.OccupancyDate > People.StartDate")
if thedb.Error then
InfoBox "Failed to count people"
else
if rs.eof then
//no records .. always ends up here and no errors
else
//something to mention
end if
end if
rs.close[/code]