search

demmings / gsSQL

Use real SQL SELECT syntax to filter your Google Sheets data using a simple custom function.
https://demmings.github.io/
GNU General Public License v3.0
23 stars 2 forks source link

Date comparison not valid when NON DATE characters are in column. #58

Open demmings opened 8 months ago

demmings commented 8 months ago

So

=gsSQL("select * from booksales where Date > '5/3/2022' ", "booksales", BookSales!A1:H)

is including records with TEXT CHARACTERS in the Date column. My comparisons to mySQL don't have this issue since I only have DATES in that SQL table. However, sheets can have anything - so I would probably think those records should be skipped.

Note This specific example may not be solvable.
When a comparison is to be made and either side of the logical comparison is an instance of Date, both sides are converted to a JS date. So the problem here is that if a column data in 'Date' is character data and '5/3/22' is character data - a regular ASCII comparison would be made. If however, '5/3/2022' references a CELL (bind variables) - it would be taken to be a date since Sheets would automatically converted it into a JS date

e.g. cell C1 was a date.

=gsSQL("select * from booksales where Date > ?1 ", "booksales", BookSales!A1:H, true, C1)
demmings commented 7 months ago

I am leaving this open for now. I need some more real world testing - rather than just my TDD tests. Dates in general have more things that can go wrong. More testing is need for any function that uses dates and data in the sheets is not perfect.