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, Apr 21 '16 at 08: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, Apr 21 '16 at 08: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, Apr 21 '16 at 08:45
`cursor.getInt(0)`, are you going to get first column value of the table?? — calvinfly, Apr 21 '16 at 08:52
I guess you should wrap time string variables with `datetime()` function — Alex, Apr 21 '16 at 08:56
I just want to know if start and enddate are empty or not. Maybe that's why it returns 0. — António Paulo, Apr 21 '16 at 09:29
@AntónioPaulo AssignDate is text in my table. I have to convert it into DATETIME to use my query ? — Noman, Apr 21 '16 at 10:55
@SanjayKakadiya for that i have to change my parsing schema also? — Noman, Apr 21 '16 at 11:51
@SanjayKakadiya No.. we can store timestamp value as TEXT in sqlite. See this link: http://www.sqlite.org/datatype3.html — Noman, Apr 21 '16 at 12:04
sure you can but you can't use the between function in mysql if the col is a text — Basil Battikhi, Apr 21 '16 at 12:10

Bharatesh · Accepted Answer · 2018-04-24T08:54:20.793

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 '18 at 08:54

answered Apr 21 '16 at 10:47

Bharatesh

8,943
3
38
67

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 ? – Noman Apr 21 '16 at 11:24
well for me TEXT type is working fine.. however try with DateTime also. – Bharatesh Apr 21 '16 at 11:28
Cool.. can you share your full function ? – Noman Apr 21 '16 at 11:29
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. – Noman Apr 21 '16 at 11:48
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/109809/discussion-between-skadoosh-and-noman-hamid). – Bharatesh Apr 21 '16 at 12:40

score 0 · Answer 2 · answered Apr 21 '16 at 08:59

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

score 0 · Answer 3 · answered Apr 21 '16 at 09:02

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

Tried that.. Not working.. AssignedDate in my table is text. — Noman, Apr 21 '16 at 11:00

Android SQLite Query between Dates not working

3 Answers3