CODEWITHSUNDEEP
androidsqliteandroid-sqlitedate-comparison
Volodymyr Shalashenko
Volodymyr Shalashenko

Reputation: 159

Troubles in comparing dates in SQLite

I'm trying to compare the current date with another one, stored in a SQLite database column and select every row in between these dates.
I can't understand why my query doesn't work.

@Override
public int getFamiliesClientsTotalNum() {
    String countQuery = "SELECT * FROM " + SQLiteHelper.FAMILIES_SERVICES_TABLE_TITLE + " WHERE DATE("
            + SQLiteHelper.FAMILIES_SERVICES_DATE
            + ") >= DATE('" + new SimpleDateFormat("yyyy-MM-dd").format(mStartDate)
            + "') AND DATE(" + SQLiteHelper.FAMILIES_SERVICES_DATE
            + ") <= DATE('" + new SimpleDateFormat("yyyy-MM-dd").format(mFinishDate) + "')";

    Cursor cursor = mSQLiteHelper.getReadableDatabase().rawQuery(countQuery, null);

    int cnt = 0;
    while(cursor.moveToNext()){
        Log.i(TAG, "countQuery: " + countQuery);
        Log.i(TAG, "today is: " + new SimpleDateFormat("yyyy-MM-dd").format(mStartDate));
            cnt++;
    }
    cursor.close();
    return cnt;
}

Upvotes: 1

Views: 110

Answers (1)

Phant&#244;maxx
Phant&#244;maxx

Reputation: 38098

It doesn't work because you are passing a value (returned by the Date() function) instead of a column name:

... DATE(" + SQLiteHelper.FAMILIES_SERVICES_DATE + ") ...

This is like writing

... WHERE Date(MyDateColumn) = '2016-04-08' ...

It indeed must be like this:

..." + SQLiteHelper.FAMILIES_SERVICES_DATE + "...

without the using the Date() function.

which is like (correctly) writing 

... WHERE MyDateColumn = '2016-04-08' ...

Note: you made the same mistake twice in your query.

[EDIT]

Using the BETWEEN ... AND ... operator is far better (and also easier to read), for specifying a date range:

... WHERE MyDateColumn BETWEEN '2015-04-08' AND '2016-04-08' ...

Upvotes: 1

Related Questions