Cannot execute query for Date column using between in SQLite

Question

I am using SQLITE database in Android.

Create table query is:

String CREATE_TBookOrder = "CREATE TABLE IF NOT EXISTS BookOrder (" +
        "  `OrderId` INTEGER ," +
        "  `OrderDate` DATE ," +
        "  `OrderTotal` DOUBLE " +
        ")";

I am trying to fetch data between dates using following query:

 SELECT OrderId , OrderTotal FROM BookOrder
 WHERE OrderId = 55 AND ( OrderDate BETWEEN '2018-01-01' AND '2018-12-12')

But I am receiving zero records in response.

And my code to fetch data is:

String queryStr = "SELECT OrderId , OrderTotal FROM BookOrder WHERE OrderId = 55 AND ( OrderDate BETWEEN '2018-01-01' AND '2018-12-12')";
sqLiteDatabase = this.getWritableDatabase();
cursor = sqLiteDatabase.rawQuery(queryStr, null);
if (cursor.moveToFirst()) {
    do {
        Log.d("data", "sub OrderId " + cursor.getInt(0));
        Log.d("data", "sub OrderTotal " + cursor.getFloat(1));
        Log.d("data", "--------------");
    } while (cursor.moveToNext());
}

sqLiteDatabase.close();

This runs smoothly on SQL Server but it's not running successfully in SQLITE Android.

SQLite has not date data type. What are the actual values you're storing in this column? — CL., Mar 15 '18 at 18:35
I changed the DataType to Text for the OrderDate in Sqlite, still no results — JavaEnthusias, Mar 19 '18 at 16:58

score 0 · Answer 1 · answered Mar 15 '18 at 19:59

Assuming that the date is stored as a string in the format yyyy-mm-dd then you need to inform the query that you are dealing with dates in the query.

So instead of :-

SELECT OrderId , OrderTotal FROM BookOrder
    WHERE OrderId = 55 
        AND ( OrderDate BETWEEN '2018-01-01' AND '2018-12-12')

You could use :-

SELECT OrderId , OrderTotal FROM BookOrder
    WHERE OrderId = 55 
        AND (date(OrderDate) BETWEEN date('2018-01-01') AND date('2018-12-12'))

You may wish to check out SQL As Understood By SQLite - Date And Time Functions

I changed the DataType to Text for the OrderDate in Sqlite, still no results. — JavaEnthusias, Mar 19 '18 at 16:58

score -1 · Answer 2 · answered Mar 15 '18 at 16:54

-1

I read your problem, I think there is the issue of the date DataType. Please check this link, maybe it solves your problem.

answered Mar 15 '18 at 16:54

Swapnil Gaikwad

44
4

I changed the DataType to Text for the OrderDate in Sqlite, still no results – JavaEnthusias Mar 19 '18 at 16:58

score -1 · Accepted Answer · answered Mar 19 '18 at 18:27

Solved It After trying a lot of options. What Worked for me is , first i changed the data type of the date column to DATETIME. then before inserting the date into the column i converted it into yyyy-MM-dd hh:mm:ss format.

And at the time of retrieving the date i used the following query ,

SELECT OrderId , OrderTotal FROM BookOrder WHERE OrderId = 55 AND ( OrderDate BETWEEN '2018-01-01' AND '2018-12-12')

where first Date is Inclusive and second date is Exclusive.

Cannot execute query for Date column using between in SQLite

3 Answers3