Android Question purge db by date range

Discussion in 'Android Questions' started by Charles, Mar 13, 2015.

  1. Charles

    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?

  2. edgar_ortiz

    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.


  3. keirS

    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

    DELETE FROM tickets WHERE ('20' || substr(ticketdate,7,2) || "-" || substr(ticketdate,1,2) || "-" || substr(ticketdate,4,2))  < "2014-12-09"
    RandomCoder and DonManfred like this.
  4. Charles

    Charles Member Licensed User


    Thanks, that worked wonderfully. I appreciate the help. :)

  5. KMatle

    KMatle Expert Licensed User

  6. Charles

    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...
  7. Charles

    Charles Member Licensed User


    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.
    DonManfred likes this.
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice