Cant select dates in SQLite

Question

The simplest query in the world isnt working in SQLite:

SELECT * FROM "events"
  WHERE "date" >= datetime('2021-08-05T22:00:00') AND 
        "date" < datetime('2021-08-12T22:00:00');

The dates in the table as follows and I only receive last 2:

2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-12T16:00:00
2021-09-10T16:00:00
2021-10-12T16:00:00
2021-11-09T17:00:00
2021-12-09T17:00:00
2021-08-11T16:00:00
2021-08-12 16:00:00

I inserted values by pasting simple text from notepad in SQLiteStudio v3.2.1

Answer 1

If you execute:

datetime('2021-08-05T22:00:00')

the result is:

2021-08-05 22:00:00

Do you notice the missing 'T'?

So, you are comparing strings (in SQLite dates are strings) and the comparison is alphabetical, but in the left side of the inequality you have a string with a 'T' and in the right side a string without a 'T'.

Either use the function datetime() in both sides of the inequality:

WHERE datetime("date") >= datetime('2021-08-05T22:00:00') 
  AND datetime("date") < datetime('2021-08-12T22:00:00')

or, don't use it at all:

WHERE "date" >= '2021-08-05T22:00:00' 
  AND "date" < '2021-08-12T22:00:00'

See the demo.

Answer 2

I think its "by design". SQLiteStudio doesnt bother to check types. So if you paste text then you get text in Date field. You have to post-process dates by hand:

update "events" set "date" = datetime("date");

EDIT to clarify. The following statements did not produce correct result before update and are producing correct result after the update

SELECT * FROM "events" WHERE "date" >= datetime('2021-08-05T22:00:00') AND "date" < datetime('2021-08-12T22:00:00');
SELECT * FROM "events" WHERE "date" >= datetime('2021-08-05 22:00:00') AND "date" < datetime('2021-08-12 22:00:00');
SELECT * FROM "events" WHERE "date" >= '2021-08-05T22:00:00' AND "date" < '2021-08-12T22:00:00';
SELECT * FROM "events" WHERE "date" >= '2021-08-05 22:00:00' AND "date" < '2021-08-12 22:00:00';