CODEWITHSUNDEEP
androidsqlitedatetimeandroid-sqlite
ghasem deh
ghasem deh

Reputation: 718

Select some data from SQLite using a date filter?

I want to select some data from a database table using a filter by date (date column is: [searchDate] DATETIME DEFAULT CURRENT_TIMESTAMP,)

In this pic, I'm using SQLite Expert and a filter by date: enter image description here

and this code is for getting the current year and month:

DateFormat YEAR_FORMAT = new SimpleDateFormat("yyyy");
        DateFormat MONTH_FORMAT = new SimpleDateFormat("MM");
        YEAR_DATE = YEAR_FORMAT.format(new Date());
        MONTH_DATE = MONTH_FORMAT.format(new Date());
        Date date = new Date();
        Log.d("Month", YEAR_FORMAT.format(date) + " " + MONTH_FORMAT.format(date));

and the cursor query in my app for getting a list filtered by date (current year):

public List<Moment> MOMENT_YEAR(String year) {
        try {
            Moment MOMENT_TABLE;
            List<Moment> MOMENTS = new ArrayList<>();
            DB_HELPER.openDatabase();
            db.beginTransaction();
            Cursor MOMENT_CURSOR = db.rawQuery("SELECT * FROM tblMoment WHERE strftime('%Y', searchDate) =" + year, null);
            db.setTransactionSuccessful();
            MOMENT_CURSOR.moveToFirst();
            while (!MOMENT_CURSOR.isAfterLast()) {
                MOMENT_TABLE = new Moment(MOMENT_CURSOR.getInt(0), MOMENT_CURSOR.getString(1), MOMENT_CURSOR.getString(2), MOMENT_CURSOR.getString(3),
                        MOMENT_CURSOR.getString(4), MOMENT_CURSOR.getString(5), MOMENT_CURSOR.getInt(6), MOMENT_CURSOR.getInt(7) != 0);
                MOMENTS.add(MOMENT_TABLE);
                MOMENT_CURSOR.moveToNext();
            }
            MOMENT_CURSOR.close();
            return MOMENTS;
        } catch (Exception e) {
            return null;
        } finally {
            db.endTransaction();
            DB_HELPER.closeDatabase();
        }
    }

I tried, but nothing happens and the list is null (empty).
I have most dates in the current year.

Upvotes: 0

Views: 3484

Answers (2)

Ankita Singh
Ankita Singh

Reputation: 314

SELECT * FROM transactionList WHERE transactionDate BETWEEN '2018-04-29' AND '2018-05-29' LIMIT 20;

try this query and it will work like wow :)

Please check the question

Filter Not Working on date

Upvotes: 0

Phant&#244;maxx
Phant&#244;maxx

Reputation: 38098

Isn't it clear? Surround the year variable with the SQL string delimiters (') in your query. 

Cursor MOMENT_CURSOR = 
    db.rawQuery("SELECT * FROM tblMoment WHERE strftime('%Y', searchDate) = '" + year + "'", null);

Or simply use a parametric query (the string delimiters will be handled for you by Android).

Cursor MOMENT_CURSOR = 
    db.rawQuery("SELECT * FROM tblMoment WHERE strftime('%Y', searchDate) = ?", new String[]{year});

Upvotes: 2

Related Questions