CODEWITHSUNDEEP
androidandroid-date
vinothp
vinothp

Reputation: 10059

how to retrieve values from the database(Sqlite) by using date in android?

I have stored the date as string in sqlite database table. What i need i i have to retrieve the values which stored in last seven days. I have tried by using following but it doesn't showing any values.

My Database function

     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('now')" + " AND " + "date('now','-7 days')", null);

       activity.startManagingCursor(cursor);

       return cursor;  
    }

Calling function

    Cursor week = db.paymentWeek(this);
               String[] weekly = new String[] { PaymentAppDataBase.REQUESTEDDATE,PaymentAppDataBase.PAYMENTNAME,PaymentAppDataBase.AMOUNT };
               int[] sevendays = new int[] { R.id.Date,R.id.Name,R.id.Amount };

               SimpleCursorAdapter weekcursor =
                    new SimpleCursorAdapter(this, R.layout.listview, week, weekly, sevendays);
                setListAdapter(weekcursor);
               db.close();

It would be helpful if you guys sort out this problem. I get stuck over here.

Upvotes: 0

Views: 1390

Answers (2)

Jackie
Jackie

Reputation: 307

You don't have to change your db design.

Just write your own convert from dateString to long method, and put it in class like DateUtil.java, then call it wherever you want to do something with date type.

The method should look like this:

    public static long convertStringDateToLong(String date, String yourDateFormat) {
    Long time = 0L;
    try {
        SimpleDateFormat df = new SimpleDateFormat(yourDateFormat);
        Date d = null;
        try {
            d = df.parse(date);
        } catch (ParseException e) {
            e.printStackTrace();
        }
        time = d.getTime();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return time;
}

where yourDateFormat should be something like: "MM/dd/yyyy"

Upvotes: 0

jeet
jeet

Reputation: 29199

try Following query to get records for last week(7 days):

"select * from " + PAYMENTTABLE + " where " + REQUESTEDDATE + "DATE_SUB( CURDATE( ) ,INTERVAL 7 DAY ) AND CURDATE( )";

Upvotes: 1

Related Questions