CODEWITHSUNDEEP
androiddatabasesqlite
Hank Wilson
Hank Wilson

Reputation: 519

Android SQLite RawQuery not returning records with 2017 date

I have a database that contains upcoming births. The file has 5 records in it with due dates of (11-15-2016,10-15-2016, 12-14-2016, 12-13-2016 and 02-12-2017) The query is

SQLiteDatabase db = dbhelper.getReadableDatabase();
        String QueryPart1="SELECT inseminations._ID, inseminations.patient_id, inseminations.date_due ";
        String QueryPart2="FROM inseminations ";
        String QueryPart3="WHERE inseminations.date_due>=? ";
        String QueryPart4="ORDER BY inseminations.date_due ASC";
        String FinalQuery=QueryPart1+QueryPart2+QueryPart3+QueryPart4;
        mCursor = db.rawQuery(FinalQuery ,howToFilter);'

howToFilter is

            String howToFilter[]={ExpectedDateDue};

ExpectedDateDue is

        ExpectedDateDue = new SimpleDateFormat("MM-dd-yyyy").format(new Date());

The query retrieves all records except for the one from 2017, the listview is sorted correctly, it's as if the query is only taking those records where the month is after the current month instead of the entire date.

Upvotes: 0

Views: 140

Answers (1)

Karakuri
Karakuri

Reputation: 38595

Comparison operators on text in SQLite use lexicographic order. What this means, for example, is "01-01-2017" is less than "05-04-2016". SQLite has no indication that these strings represent moments in time that should be compared chronologically.

In general, I dislike using strings for timestamps in my databases for reasons like this. I much prefer to store timestamps as long values representing seconds (or milliseconds) since epoch. It's far easier to compare timestamps this way, and converting to and from date strings is simple enough either in SQLite or in Java.

If that's not feasible for some reason, then I suggest you alter the way you store your date strings in the database. Instead of MM-dd-yyyy, use yyyy-MM-dd. In this format, lexicographic comparisons will work; using the same example as before, "2017-01-01" is greater than "2016-05-04". Additionally, this format is acceptable as input to SQLite's various date and time functions, should you decide to use them.

If you can't alter the way the dates are stored, you will need to somehow convert them into a format that is comparable in the way you expect. You could use SQLite's substr() and || (concatenation) functions in the query to convert the date from MM-dd-yyyy to yyyy-MM-dd format. The code in this answer demonstrates this.

Upvotes: 2

Related Questions