Android SQLite Query between Dates not working

Question

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 !

Where are you calling this method?Can you post the code thatI mentioned? — António Paulo
– António Paulo, Commented Apr 21, 2016 at 8:39
@noman-hamid : is the date format stored in your db is same as the date format of your arguement startDate and endDate ??? — Sandeep Bhandari
– Sandeep Bhandari, Commented Apr 21, 2016 at 8:44
@AntónioPaulo i am just getting count from that function into and integer variable in my activity. But the count i am getting from this query is 0. — Noman
– Noman, Commented Apr 21, 2016 at 8:45
cursor.getInt(0), are you going to get first column value of the table?? — calvinfly
– calvinfly, Commented Apr 21, 2016 at 8:52
I guess you should wrap time string variables with datetime() function — Alex
– Alex, Commented Apr 21, 2016 at 8:56

Bharatesh · Accepted Answer · 2018-04-24 08:54:20Z

2

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'

edited Apr 24, 2018 at 8:54

answered Apr 21, 2016 at 10:47

Bharatesh

8,9843 gold badges40 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Noman Over a year ago

Yes i tried that. Tell me 1 thing, i am using AssigneDate datatype as Text in table.. is it ok OR i have to use DATETIME data type ?

Bharatesh Over a year ago

well for me TEXT type is working fine.. however try with DateTime also.

Noman Over a year ago

Cool.. can you share your full function ?

Noman Over a year ago

Thanks i will try that.. i have to show in grid view ... records are for all months so i have to apply filters accordingly.. That is why i am passing date in query.

Bharatesh Over a year ago

Let us continue this discussion in chat.

ads · Accepted Answer · 2016-04-21 08:59:25Z

0

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;
}

answered Apr 21, 2016 at 8:59

ads

1,7432 gold badges18 silver badges36 bronze badges

Comments

CL. · Accepted Answer · 2016-04-21 09:02:27Z

0

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 });
}

answered Apr 21, 2016 at 9:02

CL.

182k18 gold badges241 silver badges282 bronze badges

1 Comment

Noman Over a year ago

Tried that.. Not working.. AssignedDate in my table is text.

Collectives™ on Stack Overflow

Android SQLite Query between Dates not working

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related