Charles Member Licensed User Longtime User Mar 13, 2015 #1 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. Has anyone accomplised this task? Charles
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. Has anyone accomplised this task? Charles
E edgar_ortiz Active Member Licensed User Longtime User Mar 13, 2015 #2 Because Sqlite doesn't have a date type, the best way of store a date is in a CHAR field and using a YYYYMMDD format. Regards, Edgar Upvote 0
Because Sqlite doesn't have a date type, the best way of store a date is in a CHAR field and using a YYYYMMDD format. Regards, Edgar
K keirS Well-Known Member Licensed User Longtime User Mar 13, 2015 #3 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 Code: Copy DELETE FROM tickets WHERE ('20' || substr(ticketdate,7,2) || "-" || substr(ticketdate,1,2) || "-" || substr(ticketdate,4,2)) < "2014-12-09" Upvote 0
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 Code: Copy DELETE FROM tickets WHERE ('20' || substr(ticketdate,7,2) || "-" || substr(ticketdate,1,2) || "-" || substr(ticketdate,4,2)) < "2014-12-09"
Charles Member Licensed User Longtime User Mar 14, 2015 #4 keirS: Thanks, that worked wonderfully. I appreciate the help. Charles Upvote 0
Charles Member Licensed User Longtime User Mar 14, 2015 #6 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... Upvote 0
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...
Charles Member Licensed User Longtime User Mar 16, 2015 #7 Update: 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. simpler, faster, easier. Thanks to everyone for the input. Upvote 0
Update: 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. simpler, faster, easier. Thanks to everyone for the input.