How to fetch data's between two dates in android (not sqlite)?

Question

I tried to do to fetch the data's between from and to dates. I did it on sql lite,it works and I tried in android coding part,it doesn't work,could you guys please help

protected void adddata1() throws ParseException {

Database db=new Database(Reportmonth.this);
SQLiteDatabase sb=db.getReadableDatabase();


String a=from.getText().toString();
String b=to.getText().toString();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");


 Date a1=sdf.parse(a);//convert string to date
Date b1=sdf.parse(b);//convert string to date


Cursor cus=sb.rawQuery("select * from expenz where daty between '" + a1 +"' AND '"+b1+"'",null);

cus.moveToFirst();
 for(int i=0;i<cus.getCount();i++)
     {


     if(cus!=null) //i think the error starts from this onwards.        
     {                           
         soi.setText(cus.getString(0));


         Log.i("",cus.getString(0));

          desc.setText(cus.getString(3));

          dat.setText(cus.getString(1));

          woc.setText(cus.getString(2));


          amount.setText(cus.getString(5));

          adddata();

          search.setClickable(false);

  }      
     cus.moveToNext();

     } 

    }

What do you mean by "it doesn't work"? Is it crashing? Is it getting dates in a different range? Is it drawing a picture of a chicken on the screen? — CodeMonkey, Dec 23 '14 at 05:10
If you are using `varchar` for storing date, this query will not work. May be you have to get all values and then convert the database values to `Date` and then compare. — codePG, Dec 23 '14 at 05:20

score 1 · Accepted Answer · answered Dec 23 '14 at 05:48

It is not advisable to store date in database as varchar as when it comes to situation like yours, its a real pain

Still below I have shown a work around, where you can extract all data from the table, convert the date column in the table from string to Date object and then do your comparison. But this will hit the performance a lot specially when the rows in your database is really high.

//For storing the rows that are in between the date range
ArrayList<HashMap<String, String>> rowList = new ArrayList<HashMap<String, String>>();

SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

//Important that the date should be in the same format as specified above
Date startDate = sdf.parse(a);//convert string to date
Date endDate = sdf.parse(b);//convert string to date

Cursor cus = sb.rawQuery("select * from expenz",null);

if (cursor.moveToFirst()) {
    do {
        try {
            //Converting the database `varchar date` to `Date` object here
            //Here cursor.getString(X) should be the column number of your daty column
            //And also the date format in the database should also be same as "yyyy-MM-dd"
            Date thisDate = sdf.parse(cursor.getString(X));

            // Check if the database date is within the range
            if (thisDate.after(startDate) && thisDate.before(endDate)) {
                HashMap<String, String> map = new HashMap<String, String>();

                //Here have the number of columns you want to have according to your database
                map.put("column0", cursor.getString(0));
                map.put("column1", cursor.getString(1));
                map.put("column2", cursor.getString(2));
                map.put("column3", cursor.getString(3));
                map.put("column4", cursor.getString(4));
                map.put("column5", cursor.getString(5));

                rowList.add(map);
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
    } while (cursor.moveToNext());
}

// Now here the rowList will have the rows between the specified date range

You could modify the inner most if loop to meet your requirements.

I hope it helps!

Thanks dude..you saved me...I have a doubt if the given date also have the records means how we show in if condition. — user3736314, Dec 23 '14 at 08:18
i gave a start date and end date it displays in between dates data's,if the start and end date also have a record means how to display start and end date date data's also? — user3736314, Dec 23 '14 at 08:23
In that case, try subracting one day from start date and adding one day to the end date. — codePG, Dec 23 '14 at 08:42
You can refer this answer to add and subract date http://stackoverflow.com/questions/11882926/how-to-subtract-x-day-from-a-date-object-in-java — codePG, Dec 23 '14 at 08:46

How to fetch data's between two dates in android (not sqlite)?

1 Answers1