CODEWITHSUNDEEP
androidsqliteandroid-studioandroid-sqlite
atheesh_27
atheesh_27

Reputation: 308

Android SQLite rawquery with Date String in WHERE clause not working

I want to get all data if today date is in between two dates that i have already stored in the table as strings in (in yyyy-MM-dd format). I tried several ways but nothing worked out.

If i try this in following way it is working fine and receiving correct values.(count = 4)

public int getMedicineDataFromDate() {

    SQLiteDatabase db = this.getReadableDatabase();
    String dates = "'2018-11-26'";
    String stext = "SELECT * FROM " + MED_TABLE_NAME + " WHERE " + dates + " BETWEEN STARTING_DATE AND ENDING_DATE";

    @SuppressLint("Recycle") Cursor cursor = db.rawQuery(stext, null);

    return  cursor.getCount();

}

But what i want to do this by passing date from my activity as in following way.And this way i get empty cursor (count = 0).

Inside my activity

 Date today = Calendar.getInstance().getTime();
 SimpleDateFormat e = new SimpleDateFormat("yyyy-MM-dd");
 String todayString = e.format(today);

 int result = mydb.getMedicineDataFromDate(todayString);
 String resl = Integer.toString(result);

 Toast.makeText(MainActivity.this,"Data received " + resl,Toast.LENGTH_LONG).show();

In DatabaseHelper class

 public int getMedicineDataFromDate(String date) {

    SQLiteDatabase db = this.getReadableDatabase();
    String stext = "SELECT * FROM " + MED_TABLE_NAME + " WHERE " + date + " BETWEEN STARTING_DATE AND ENDING_DATE";

    @SuppressLint("Recycle") Cursor cursor = db.rawQuery(stext, null);

    return  cursor.getCount();

}

If someone can explain me why i am getting 0 as the count in second way and what is the correct way to do this.it will be very helpful to me.

Upvotes: 3

Views: 1031

Answers (2)

vimukthi
vimukthi

Reputation: 263

I think problem is there.

 SimpleDateFormat e = new SimpleDateFormat("yyyy-MM-dd");
 String todayString = e.format(today);

You change todayString value as String like 

  String todayString ="'"+e.format(today)+"'";

this.

Upvotes: 1

MikeT
MikeT

Reputation: 56953

Your issue is that you have not enclosed the String in single quotes and therefore it is interpreted as if it is numeric and thus a calculation.

That is the resultant SQL (assuming the date is 2018-11-26 which mathematically is 2018 minus 11 minus 26 = 1981) would be :-

SELECT * FROM your_table WHERE 1981 BETWEEN STARTING_DATE AND ENDING_DATE

You could change String todayString = e.format(today); to be String todayString = "'" + e.format(today) + "'";

Or you could use :-

String stext = "SELECT * FROM " + MED_TABLE_NAME + " WHERE ? BETWEEN STARTING_DATE AND ENDING_DATE";
Cursor cursor = db.rawQuery(stext, new String[]{date}); //<<<<<<<<<< will place quotes around the string for you

Or you could use :-

public int getMedicineDataFromDate(String date) {

    SQLiteDatabase db = this.getReadableDatabase();
    String whereclause = "? BETWEEN STARTING_DATE AND ENDING_DATE";
    String[] whereargs = new String[]{date};
    Cursor cursor = db.query(MED_TABLE_NAME,null,whereclause,whereargs,null,null,null);
    int rv = cursor.getCount();
    cusror.close();
    return  rv;   
}

  • Note in all cases you should close the Cursor, as is shown in the last option, before returning, otherwise the Cursor's remain open and you have the potential of an exception due to too many being open.

  • The above is in-principle code and has not been checked or run and therefore may have some errors.

Upvotes: 0

Related Questions