How to select records where date is between date1 and date2?

Question

SQLiteDatabase db = DatabaseProvider.dbHelper.getReadableDatabase();
Cursor cursor = db.rawQuery("Select * from " + ConsumerEnquiryTable.TABLE_NAME + " where "
            + ConsumerEnquiryTable.Cols.USER_LOGIN_ID + "='" + userId + "' AND ( CAST("
            + ConsumerEnquiryTable.Cols.DUE_DATE + " as datetime) >= CAST(" + beginDate  +
            " as datetime) AND CAST(" + ConsumerEnquiryTable.Cols.DUE_DATE + " as datetime) <= CAST("+ endDate + " as datetime)) AND "
            + ConsumerEnquiryTable.Cols.CARD_STATUS + "='" + jobCardStatus + "'", null);

I have tried with CAST, strftime, datetime. The due_date column is of varchar type and i want to select records having due_date between beginDate and endDate. All the dates are of dd/MM/yyyy format

Please elaborate on your question and add further details. Please avoid posting code-only content. Please make use of the SO formatting features and post your code as "code". — Korashen, Apr 16 '19 at 10:50
please check this : https://stackoverflow.com/questions/14207494/android-sqlite-select-between-date1-and-date2 — Prashanth Verma, Apr 16 '19 at 10:52

score 1 · Accepted Answer · answered Apr 16 '19 at 17:06

If you change the column's DUE_DATE format to YYYY-MM-DD, which is the only comparable format for SQLite, then all you need is a BETWEEN clause and pass all the arguments as a string array, which is the recommended safest way to avoid any risk of sql injection:

Cursor cursor = db.rawQuery(
    "SELECT * FROM " + ConsumerEnquiryTable.TABLE_NAME + " WHERE " + 
    ConsumerEnquiryTable.Cols.USER_LOGIN_ID + " = ? AND (" +
    ConsumerEnquiryTable.Cols.DUE_DATE + " BETWEEN ? AND ?) AND " +
    ConsumerEnquiryTable.Cols.CARD_STATUS + " = ?", 
    new String[] {userId, beginDate, endDate, jobCardStatus}
);

Of course the arguments beginDate and endDate must also be in the YYYY-MM-DD format.

score 0 · Answer 2 · answered Apr 16 '19 at 11:34

0

You can use following query

mDb.query(MY_TABLE, null, DATE_COL + " BETWEEN ? AND ?", new String[] {
                minDate + " 00:00:00", maxDate + " 23:59:59" }, null, null, null, null);

answered Apr 16 '19 at 11:34

Ankit Tale

1,924
4
17
30

returns no records, my date column is of varchar type – khushboo Apr 16 '19 at 12:00

How to select records where date is between date1 and date2?

2 Answers2