Hello Sql Ninjas. Need some help...

This works: SELECT * FROM Pairings WHERE PDays = 3

And This works:

SELECT * FROM Pairings WHERE (DATETIME(PStartDate) > DATETIME('2022-03-02 00:00:00') AND DATETIME(PEndDate) < DATETIME('2022-03-07 00:00:00'))

But, this does not work (What am I missing?)?

SELECT * FROM Pairings WHERE (( DATETIME(PStartDate) > DATETIME('2022-03-02 00:00:00') AND DATETIME(PEndDate) < DATETIME('2022-03-07 00:00:00')) AND (PDays > 0))

This last query returns 0 rows (no errors). (should return over 50 rows). Thanks

Can you show us the CREATE TABLE command for the table Pairings ? I'm currently interested to see if you declared an affinity for the PDays column, but it can help in other ways.

Also you might try

SELECT PDays,typeof(PDays) FROM Pairings

(possibly with some WHERE clauses) and see whether you get back what you expect.

Thank you thank you. Pdays is of type int The first two queries work and return correct results .

Its the last query that does not work.

Do you KNOW that Pdays is an integer, or are you ASSUMING that Pdays is an INTEGER?

Execute the following query and see what is the result:

select typeof(Pdays) from pairings group by typeof(PDays);

If it results anything other than INTEGER and NULL then Pdays is NOT always an INTEGER and the Pdays OP constant will not work as you appear to intend but rather will work as designed and documented (which is not the same thing, unless your expectation is working as designed and documented).

If, in fact, Pdays is an INTEGER (that is, the query above returns only INTEGER and NULL) then SQLite3 has been designed and documented to work as you seem to expect, however, if the above query returns anything other than INTEGER and NULL then the expression Pdays OP 0 will work as designed and documented (which is different, apparently, from your expectation).

It would be perspicacious to actually test your data and make sure that it is what you expect it to be before complaining that there is a problem.

Again Thank you thank you :)

select typeof(Pdays) from pairings group by typeof(PDays);

• returns Integer.
  

Here is the Table created with C# in Xamarin.

private readonly SQLiteConnection _databaseNA;
_databaseNA.CreateTable<Pairings>();

public class Pairings
{
    [PrimaryKey, AutoIncrement]
    public int PID { get; set; }
    public string PName { get; set; }
    public string PDate { get; set; }
    public string PStartDate { get; set; }
    public string PEndDate { get; set; }    
    public int PDays { get; set; } //This is the number of days
    public int PLegTotal { get; set; }
    public string PFirstReport { get; set; }
    public string PLastReport { get; set; }
    public string PAllText { get; set; } //
  }

Oh my, This looks like an issue with the SQLite or Linq namespaces that I am using. So wierd. I just downloaded DB Browser for SQLite and ran the query - It works fine...

SELECT * FROM Pairings WHERE ((PDays = 3) AND DATETIME(PStartDate) > DATETIME('2022-03-07 00:00:00') AND DATETIME(PEndDate) < DATETIME('2022-03-16 00:00:00'))

Somehow this (below) SQLite method/statement is not returning the rows. While it does return the rows for the first two statements in the first post.

return _databaseNA.Query<Pairings>(QueryStringHere);

Yikes...

The canonical program to try things in is the SQLIte shell tool, which is a character-based app you can download from the SQLite site. It was written by the same team which maintains SQLite itself. If your query works properly in that tool, but doesn't work in something else, you can be 99% certain the bug is not in SQLite.

Thank You Thank You. Appreciate the help!!!

I downloaded and used a tool called DB Browser For SQLite. Works nice. Not sure of its validity so I ran it in a VM.

My issue was the DATETIME Statement. My DateTime that I was passing in was in the correct format but included the AM or PM. Once I removed that and used a 24 hr time - It worked fine.

Thanks again