How to filter rows by using datetimes present in the row and datetime('now')

Question

Given the table Use, defined as

CREATE TABLE Use(date TEXT NOT NULL, id TEXT PRIMARY KEY);

I want to make a query to find "All uses before today", however SQLite is not behaving as I expect and return the wrong value.

I tried to query using my table as such:

SELECT * FROM Use WHERE date <= datetime('now')

yes. Marking this as a duplicate – LeoColman Nov 28 '22 at 18:28 — LeoColman, Nov 28 '22 at 18:28

score 0 · Answer 1 · answered Nov 27 '22 at 23:08

The problem here is that date might not be in the same format that datetime returns.

You need first to convert the date to use the same format, by using the datetime function on it as well, and then you can compare both correctly:

SELECT * FROM Use where datetime(date) <= datetime('now')

1 Answers1