how to extract data from sqlite based date constraint

Question

I am doing an application which contain a table named 'Purchase'using sq lite. table having three fields id,purchase amount,date. As i went through Google i came to know that date field is stored in sq lite as integer or text. So i used var char to store text. Now my problem is that i want to display details from the table based on the date constraint (example : data between 27-7-2012 to 10-8-2013) how can i extract the data since date field is declared as var char.. Can any body help...

You can use query straight into the DBAdapter class. You want example of such a thing? I'll post it as an answer . FYI, i have declared Date as Text (e.g 27/12/2013) — Dhaval, Nov 19 '13 at 05:07
if i use direct query also how can compare the month of the date as it is string if i want to display last 2 months data how can i write the query Can you show any small example — Mohammed Shehnad, Nov 19 '13 at 05:17
If you want adapter and total sample of showing records in Custom ListView, i can post it as well. Would you like me to, or you have it covered? — Dhaval, Nov 19 '13 at 05:30
How is your data stored? Is `TEXT`, right, but which format? `d/m/y`? — LS_ᴅᴇᴠ, Nov 19 '13 at 10:22

score 0 · Answer 1 · answered Nov 19 '13 at 05:29

This is a method i use in DBAdapter class :

public Cursor getAllDetailsField(String date) {

        return db.rawQuery("select * from " + TABLE_NAME_FIELDW + " where "
                + KEY_DATE + " = '" + date + "'", null);
    }

Here Date is passed as parameter, which means when i use this method in Activity i'll have to assign a Date to the method, so it'll execute the query. The query above simply gets the record on the date which i pass in activity.

In Activity :

private void loadFieldDatabase() {

         String date = app.getDARDate(); 
            // DATE is FETCHED FROM APPLICATION CLASS OBJECT, YOU CAN SET IT
            // IN PREVIOUS OR CURRENT ACTIVITY , HOWEVER YOU MAY FETCH ANY WAY  
            // YOU WANT TO. JUST MAKE SURE WHATEVER YOU PASS, HOLDS THE DATE VALUE

                 String date = app.getDate();
        Cursor c = db.getAllDetailsField(date);
        if (c != null && c.getCount() > 0) {
            c.moveToFirst();
            for (int count = 0; count < c.getCount(); count++) {

                DBbeanclass detail = new DBbeanclass();

                detail.setDate(c.getString(c
                        .getColumnIndex(DARDBAdapter.KEY_DATE)));
                detail.setName(c.getString(c
                        .getColumnIndex(DARDBAdapter.KEY_NAME)));

                arrCustomerField.add(detail);
                c.moveToNext();
            }
            c.close();
        }
    }

You'll see i haven't written the query for you. But i think you can do it on your own

I am having 1 more doubt, I am new in android so please asking regret if this is a foolish question. My question is : is this query return the data of a particular date...for example if i have the date 25-7-2013 the query will return all the purchase detail in that date..? But i want from that day to till now — Mohammed Shehnad, Nov 19 '13 at 05:48
Yes it returns data of a specific date. But as i have written in answer below you should try to make query by yourself. I solved the same issue you are having after 3 days of failure. I didn't even get help from SO. Try it yourself. I'll try it myself as well. I am a beginner as well, for the record. — Dhaval, Nov 19 '13 at 06:00
Link 1 : http://stackoverflow.com/questions/10680793/sql-query-to-return-the-rows-between-two-dates LINK 2: http://stackoverflow.com/questions/14681815/query-to-select-data-between-two-dates-with-the-format-m-d-yyyy If you still get the problem after referring those links let me know. — Dhaval, Nov 19 '13 at 06:02

score 0 · Answer 2 · answered Nov 19 '13 at 10:30

Consider using SQLite appropriate date formats.

Using a dates in YYYY-MM-DD format will greatly simplify your code, allowing faster in-database (without needing to fetch every row to evaluate on code) and also makes it possible to take advantage performance using indexes.

Converting your existing data is also easy to do.

how to extract data from sqlite based date constraint

2 Answers2