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

Question

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.

That's going to be tough but maybe somebody knows a way. Is it possible to change your database design? Storing dates in your database as millis (not strings) would make your life easier. — Roger, Apr 09 '12 at 01:23
you can create an array of nos of days and use in for eg. select * from table where date in('4/8/2012','4/9/2012',....);is not efficient but will work with strings. — Its not blank, Apr 09 '12 at 03:53
Thanks for your comment... Ya i can change my db as millis.. It would be great if you give some tips how to do that because i am new to android programming... Thanks — vinothp, Apr 09 '12 at 07:55

score 1 · Accepted Answer · answered Apr 09 '12 at 04:35

1

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

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

answered Apr 09 '12 at 04:35

jeet

29,001
6
52
53

Thanks for your comment.. I have tried it but it shows 04-09 07:59:15.314: E/AndroidRuntime(314): android.database.sqlite.SQLiteException: near "7": syntax error: , while compiling: select * from paymentdetails where requesteddateDATE_SUB( CURDATE( ) ,INTERVAL 7 DAY ) AND CURDATE( ) – vinothp Apr 09 '12 at 07:56

score 0 · Answer 2 · answered Dec 10 '13 at 07:05

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"

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

2 Answers2