CODEWITHSUNDEEP
androidandroid-sqlite
Tejas Shelke
Tejas Shelke

Reputation: 1

SQLite Query not working when dates are compared in where clause

I want to Fetch records between two dates from SQLite table, But it doesn't work when Dates are compared in where clause, actually it works for other String column used as condition or when no where clause is specified, so is there any other way to use dates in where clause.

cursor = db.rawQuery("SELECT min(activity_date), max(activity_date) FROM tbl_activity_details" , null);
if(cursor != null) {
    itemsList.clear();
    while(cursor.moveToNext()) {
        itemsList.add(cursor.getString(0));
        itemsList.add(cursor.getString(1));
    }
    cursor.close();
    try {
        fromDate = sdf.parse(itemsList.get(0));
        toDate = sdf.parse(itemsList.get(1));
    } catch(Exception e) {
        e.printStackTrace();
    }

    try {
        Toast.makeText(this, "here "+sdf.format(fromDate)+"  "+sdf.format(toDate), 5000).show();
        //Toasts 2015-04-23 2015-05-01
        String fd = sdf.format(fromDate);
        String td = sdf.format(toDate);

        cursor1 = db.rawQuery("SELECT * FROM tbl_activity_details WHERE activity_date BETWEEN '"+fd+"' AND '"+td+"'", null); 
        Toast.makeText(this, "here 1"+cursor1.getCount(), 5000).show();
        //Toasts here 1 10
        if(cursor1 != null) {
            while(cursor1.moveToNext()) {
                Toast.makeText(this, "here 2"+cursor1.getString(0), 5000).show();
            }

        } else {
            Toast.makeText(this, "No Record Found", 5000).show();
        }
        cursor1.close();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        db.close();
    }

Upvotes: 0

Views: 961

Answers (2)

Cai
Cai

Reputation: 5323

I've asked a similar question and the answer might help you out, here's the question.

A quick snippet from the answer that could be relevant to you:

If you're use String as Data type in SQLite, you have to format System.currentTimeMillis() into Date format "yyyy/MM/dd". If you use other formats such as M/d/yyyy --> You will have date String comparing issues. See issue below for M/d/yyyy format:

"5/15/2015".compareTo("11/30/2015") ---> Return 4 > 0
--> means "5/15/2015" > "11/30/2015" --- Wrong

Upvotes: 1

Ankit Kumar
Ankit Kumar

Reputation: 3723

Date can be compared like this.. This may help you. 

   select *  from MyTable 
   where mydate >= Datetime('2000-01-01 00:00:00') 
   and mydate <= Datetime('2050-01-01 23:00:59')

Upvotes: 0

Related Questions