Search code examples
sqlitedate-comparison

Write an SQLite query that returns all records with dates older than a certain value

I have a table with 5 columns, one of which is LastVisit (the date of the last visit to a restaurant). I want to write a query that will return all the restaurants that are liked but have not been visited for more than three months, but I am making some kind of mistake with the date functions (I think).

If I make my database and insert these values:

CREATE TABLE MyRestaurants(Name VARCHAR, Cuisine VARCHAR, Distance INTEGER, LastVisit VARCHAR, Enjoy INTEGER);

INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy) 
VALUES ('Dakshin', 'Indian', 5, 2019-01-13, 1);

INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy) 
VALUES ('Cactus', 'Mexican', 6, 2018-01-4, 1);

Then I make this query:

SELECT Name 
FROM MyRestaurants 
WHERE Enjoy = 1 AND DATE(LastVisit) <= DATE('now', '-3 month') 
ORDER BY Name;

I expect it to return Cactus, but it returns both Cactus and Dakshin.

I found this thread: SQLite return records where date is more than a week old

But when I try >= it returns no values, when I try just LastVisit instead of DATE(LastVisit) it returns no values, and when I try 90 days instead of 3 months it returns no values. I don't understand what I'm doing wrong.

Solution

  • Your insert statements seem to have a problem, because the date literals are just string literals, and so should be wrapped in single quotes:

    INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy)
VALUES ('Dakshin', 'Indian', 5, '2019-01-13', 1);

INSERT INTO MyRestaurants (Name, Cuisine, Distance, LastVisit, Enjoy)
VALUES ('Cactus', 'Mexican', 6, '2018-01-04', 1);

    Then, if you want records which did not occurr in the last 3 months you should use:

    SELECT Name
FROM MyRestaurants
WHERE Enjoy = 1 AND LastVisit <= DATE('now', '-3 month')
ORDER BY Name;

    Note that there is no formal date type in SQLite. Rather, dates are just stored as TEXT. You correctly used an ISO format for your date literals, which were correct, other than possibly that you didn't escape them in single quotes.