Android SQLite rawquery with Date String in WHERE clause not working

Question

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.

Answer 1

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.