如何选择日期在 date1 和 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);

我尝试过使用 CAST、strftime、datetime。due_date 列是 varchar 类型，我想选择在 beginDate 和 endDate 之间具有到期日期的记录。所有日期均为 dd/MM/yyyy 格式

Answer 1

如果您将列的DUE_DATE格式更改YYYY-MM-DD为 SQLite 唯一可比较的格式，那么您只需要一个BETWEEN子句并将所有参数作为字符串数组传递，这是避免任何 sql 注入风险的推荐最安全的方法：Cursor cursor = db.rawQuery(&nbsp; &nbsp; "SELECT * FROM " + ConsumerEnquiryTable.TABLE_NAME + " WHERE " +&nbsp;&nbsp; &nbsp; ConsumerEnquiryTable.Cols.USER_LOGIN_ID + " = ? AND (" +&nbsp; &nbsp; ConsumerEnquiryTable.Cols.DUE_DATE + " BETWEEN ? AND ?) AND " +&nbsp; &nbsp; ConsumerEnquiryTable.Cols.CARD_STATUS + " = ?",&nbsp;&nbsp; &nbsp; new String[] {userId, beginDate, endDate, jobCardStatus});当然，参数 beginDate 和 endDate 也必须采用YYYY-MM-DD格式。

Answer 2

您可以使用以下查询mDb.query(MY_TABLE,&nbsp;null,&nbsp;DATE_COL&nbsp;+&nbsp;"&nbsp;BETWEEN&nbsp;?&nbsp;AND&nbsp;?",&nbsp;new&nbsp;String[]&nbsp;{ &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;minDate&nbsp;+&nbsp;"&nbsp;00:00:00",&nbsp;maxDate&nbsp;+&nbsp;"&nbsp;23:59:59"&nbsp;},&nbsp;null,&nbsp;null,&nbsp;null,&nbsp;null);