SQLLite Date Compare oddness

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]

in one example you say > but in the next you say =

the same query should work the same, as it is SQLite engine that executes it, not the Browser, and not Xojo

Nope.

The first example equates occupantref and PER_REF_NO

The second adds an extra condition which compares the date

I know.
Which is why I dont understand it.

Open the same database in my app and online
Run the SQL in my app, no records no errors.

Copy the SQL and paste it online, 3 records.

when you say “ONLINE” are you referencing a DIFFERENT copy of the database???

or did you mean using SQLite in “terminal” mode against the same local copy?

I mean i go to that webs site, and browse for the same db file that my app opens.
It uploads and then I ca query against it onlien

I hate to think of the number of times I pounded my head against this same wall, only to realize that my code was pointing to a different file. That’s where I would start. Look at the paths very carefully.

Caching, iCloud, God knows…

I came back to it tonight and it’s working.
I’ve searched the drive and can only find one file of the name, but hey ho.
Thanks for making me look again… would have been awake all night otherwise. :slight_smile: