Search code examples
androidsqliteandroid-studioandroid-sqlitedate-comparison

Comparing dates in android sqlite database

I'm comparing two dates in android sqlite database I stored dates in the format

YYYY-MM-DD

Cursor cursor=db.rawQuery("SELECT * FROM "+tableName+" WHERE SALESDATE BETWEEN '2020-1-01' AND '2020-2-01';",null);

It gives result with dates of month 10, 11 and 12 along with the dates between above specified dates. I would like to know if it is a bug or is there any mistake in my code.

Solution

  • The problem here is that your date literals are in a non standard (and likely incorrect) format. Appreciate that the following inequality holds true:

    '2020-10-01' > '2020-1-01' AND '2020-10-01' < '2020-2-01'

    This is true because the text 10 is lexicographically larger than just 1, but also less than 2. To avoid this problem, use proper date literals:

    String sql = "SELECT * FROM " + tableName + " WHERE SALESDATE BETWEEN '2020-01-01' AND '2020-02-01';"
Cursor cursor = db.rawQuery(sql, null);

    Note that SQLite does not actually have a formal date type. Thus, it is very important to always store your dates in SQLite using a proper ISO format.