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
– CodeMonkey, Commented Dec 23, 2014 at 5: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
– codePG, Commented Dec 23, 2014 at 5:20

codePG · Accepted Answer · 2014-12-23 05:48:06Z

1

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!

answered Dec 23, 2014 at 5:48

codePG

1,7441 gold badge12 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user3736314 Over a year ago

Thanks dude..you saved me...I have a doubt if the given date also have the records means how we show in if condition.

codePG Over a year ago

Glad I could help. Could you please explain a little more.

user3736314 Over a year ago

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?

codePG Over a year ago

In that case, try subracting one day from start date and adding one day to the end date.

codePG Over a year ago

You can refer this answer to add and subract date stackoverflow.com/questions/11882926/…

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related