Search code examples
androidandroid-datepickerandroid-date

SQL Between operation in android sqlite date is not working?

I am new to android development. I been working on this for one day and i couldn't figure out whats the exact problem is because my query is running without error but no result.

My database query Value of formattedDate

 Calendar c = Calendar.getInstance();  

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

    String formattedDate = df.format(c.getTime());

  public Cursor paymentWeek(Activity activity)
    {

         String[] from = { _PAYMENTID, NAME, REQUESTEDDATE, FROMAD, TOADD, EMAILBODYPAYMENT, AMOUNT};  

         SQLiteDatabase db = getReadableDatabase(); 

         String orderby = REQUESTEDDATE+" DESC";   

         Cursor cursor = db.rawQuery("SELECT * FROM " + PAYMENTTABLE+ " WHERE " + REQUESTEDDATE + " BETWEEN date('"+formattedDate+"') AND date('"+formattedDate+"','-7 days')", null);

         activity.startManagingCursor(cursor);

         return cursor;  
    }

My database date type is TEXT. i am storing in the format of (yyyy-mm-dd),eg:2012-04-07

But the problem is i can retrieve all datas from the database but if i want to retrieve values for last 7 days it doesn't show any values.

It would be great if any alternatives available? and tip how to go for it?

Thanks...

Solution

  • as you say, your date type is TEXT, i dont think you can use between x and y to make the filter to return the content from x to y .

    you can try this: http://www.roseindia.net/sql/sql-between-date.shtml or http://www.techonthenet.com/sql/between.php .