Android Question purge db by date range

Charles

Member
Licensed User
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
 

edgar_ortiz

Active Member
Licensed User
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
 

keirS

Well-Known Member
Licensed User
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"
 

Charles

Member
Licensed User
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
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.
 
Top