CODEWITHSUNDEEP
androidsqlite
Noman
Noman

Reputation: 4109

Android SQLite Query between Dates not working

I have to get all records count from table. My Approach is as follows: 

  public int getTasksByStatusIDBetweenDates(int statusID, String startDate, String endDate) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(
            "select * from Task" +
                    " where StatusID=" + statusID + " and AssignDate between '" + startDate + "' and '" + endDate + "'", null);

    int total = 0;
    if (cursor.moveToFirst())
        total = cursor.getInt(0);

    cursor.close();
    return total;
}

Currently it is returning me 0. My date format is "yyyy-MM-dd HH:mm:ss"

Please guide me where i am doing wrong. Thanks !

Upvotes: 0

Views: 1679

Answers (3)

Bharatesh
Bharatesh

Reputation: 9009

Or to existing code just call cursor.getCount();

total=cursor.getCount();

Cursor.getCount();
Returns the numbers of rows in the cursor.

UPDATED
Query to get records b/w two date SO - How to select data between two date range in android SQLite

Query is
SELECT * FROM mytab where my_date BETWEEN '2016-03-01 00:00:00' AND '2016-03-19 00:00:00'

Upvotes: 2

CL.
CL.

Reputation: 180060

A query with SELECT * returns all columns of all rows of the table.

cursor.getInt(0) returns the value of the first column of the current row of the cursor.

To count rows, you could use a query with SELECT COUNT(*) ..., and read the value returned by that. Alternatively, use a helpful helper function that constructs the query for you:

public int getTasksByStatusIDBetweenDates(int statusID, String startDate, String endDate) {
    SQLiteDatabase db = this.getReadableDatabase();
    return DatabaseUtils.queryNumEntries(
            db, "Task",
            "StatusID=" + statusID + " and AssignDate between ? and ?",
            new String[]{ startDate, endDate });
}

Upvotes: 0

ads
ads

Reputation: 1723

I think you should be using COUNT(*) instead of just *.

Try this.

public int getTasksByStatusIDBetweenDates(int statusID, String startDate, String endDate) {

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery(
            "select count(*) from Task" +
                    " where StatusID=" + statusID + " and AssignDate between '" + startDate + "' and '" + endDate + "'", null);

    int total = 0;
    if (cursor.moveToFirst())
        total = cursor.getInt(0);

    cursor.close();
    return total;
}

Upvotes: 0

Related Questions