I have a database, sqlite3, where trip tickets are stored by date. Since sqlite doesn't have a date type i had to use text type. Now I want to be able to purge the database of old tickets based on a date range but no query i've tried will select or delete records by date correctly.
DELETE FROM tickets WHERE ticketdate < '12/09/14'; deletes everything except 12/09/14 through 12/31/14 tickets.
As edgar_ortiz says you should store your dates in YYYYMMDD format. I actually use YYYY-MM-DD because that is what SQLite uses for it's date functions. However in your example above this should do what you want
B4X:
DELETE FROM tickets WHERE ('20' || substr(ticketdate,7,2) || "-" || substr(ticketdate,1,2) || "-" || substr(ticketdate,4,2)) < "2014-12-09"
In computer speak the Jabanese have the best practice, admitted.
In daily people speak the american practice emphasizes the most import fact about the date which is the day...
er. well, no, well ok, it just seems right, that's all I can say.
I'm sure your logic is flawed somewhere, I think...
In order to reduce complexity and improve data integrity I changed my date format to yyyy/mm/dd in the database and added datedialog to assure that the user entered date was valid. The datedialog also returns data that is easily formatted to any style desired.