Android retrieve result after gives two parameter date and id

Question

I have two parameter (tenant_id and dateIn). with my query below I always get -1 as result. I don't know where is the issue in query statement.

Kindly note that the date format in data base is dd/MM/yyyy

        public final static String ID = "id";
 public final static String TENANT_ID = "tenant_id";
   public final static String DEBT_CURRENT_VALUE = "debt_current_value";
public final static String DEBT_SUM = "debt_sum_value";
public final static String DEBT_DATE = "debt_date";

    public long getTenantDateDebtByIdandDate(long id, String dateIn) {
    long  idout = -1;
    String[] columns = {ID, TENANT_ID, DEBT_CURRENT_VALUE,DEBT_SUM, DEBT_DATE };
    String selection = "TENANT_ID = " + id + " AND " + "DEBT_DATE < " + "date("+ dateIn+")";
    Cursor cursor = mDatabase.query(TABLE_TENANT_DEBT, columns, selection, null, null, null, null);
    if (cursor.moveToNext()) {

        idout = cursor.getLong(cursor.getColumnIndex(DEBT_DATE));
    }
    cursor.close();
    return idout;
}

`TABLE_TENANT_DEBT` is the table and DEBT_DATE is the column. one exemple of value in DEBT_DATE is 10/09/2016 — B Aristide, Sep 19 '16 at 20:01
`dd/MM/yyyy` carries no meaning to sqlite. it is a string, not a date, and therefore the comparison will not work as you expect. Use a comparable format, such as a timestamp, or a format like `yyyy/MM/dd` — njzk2, Sep 19 '16 at 20:10
I have updated the date format (from `dd/MM/yyyy` to `yyyy/MM/dd` as you suggested, but the issue is still there — B Aristide, Sep 19 '16 at 23:34

score 0 · Answer 1 · edited May 23 '17 at 12:16

0

I think there is a mistake with the column names at least

Isn't this: String selection = "TENANT_ID = " + id + " AND " + "DEBT_DATE < " + "date("+ dateIn+")"; checking columns named TENANT_ID and DEBT_DATE, when actually you want to check columns "tenant_id" and "debt_date".

So I think the selection should be instead: String selection = TENANT_ID + " = " + id + " AND " + DEBT_DATE + " < " + "date("+ dateIn+")";

I'm not so familiar with dates in sqlite, but if the date function is not working in this case, maybe you should look into the strftime function of sqlite

Also, I would recommend using the where arguments. So basically instead of saying TENANT_ID + " = " + id, you would say TENANT_ID + " = ?", and change the DEBT_DATE in similar way. Then you would give the query function one more array having the values that are then used in the places of the questionmarks in the order they are in the array. The query is safer that way, see more details why to use the where args and a complete example in this Stackoverflow thread in case you are interested.

edited May 23 '17 at 12:16

Community

1
1

answered Sep 19 '16 at 23:14

Pete

280
3
7

I have update the selection to `String selection = TENANT_ID + " = " + id + " AND " + DEBT_DATE + " < " + "date("+ dateIn+")";` but no change – B Aristide Sep 19 '16 at 23:37
I think if you don't want to use the stftime, your date should be in format yyyy-MM-dd. I tried with sqlite database creating a test table (datetest) with one column called order_date with only one row having value 2011-08-15. With that a query: select date(order_date) from datetest where order_date < '2011-08-16'; returns the row, but query select date(order_date) from datetest where order_date < '2011-08-15'; doesn't – Pete Sep 20 '16 at 07:53
If it still doesn't return any rows after that, make sure the date you pass to the function is also in the same format, and that the database has entries that have earlier date than what you pass in and the id you pass to the query – Pete Sep 20 '16 at 07:55

score 0 · Answer 2 · answered Sep 20 '16 at 20:42

Finally with the feedback of Peter and some resaerch I found the solution

  public String getTenantDateDebtByIdandDate(long id, String dateIn) {
    String  idout = "";
    String[] columns = {TENANT_ID, DEBT_DATE };
   String selection = TENANT_ID + " = '" + id + "' AND " + DEBT_DATE + " < " + "'"+ dateIn + "'";
    Cursor cursor = mDatabase.query(TABLE_TENANT_DEBT, columns, selection, null, null, null, null);
    while (cursor.moveToNext()) {

        idout = cursor.getString(cursor.getColumnIndex(DEBT_DATE));
    }
    cursor.close();
    return idout;
}

Android retrieve result after gives two parameter date and id

2 Answers2