Cannot retrieve database record using WHERE on DATE column of SQLite in Android

Question

I am developing a tutorial project and working with SQLite database. I am trying to retrieve records using WHERE on DATE column in database. But it is retrieving nothing. What is wrong with my code ?

The CREATE statement in database helper class

private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " ("
                                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," 
                                + COLUMN_DESCRIPTION + " TEXT," 
                                + COLUMN_DATE + " DATE,"
                                + COLUMN_DONE + " BOOLEAN)";

This is how I am retrieving record

String date = "04-22-2014";
ArrayList<Task> tasks = new ArrayList<Task>();
db = getReadableDatabase();
String query = "SELECT * FROM "+TABLE_NAME + " where "+ COLUMN_DATE + " = "+ date;
Cursor cursor = db.rawQuery(query,null);
if(cursor.moveToFirst())
{
    do{
        Task task = new Task();
        task.setId(cursor.getInt(0));
        task.setDescription(cursor.getString(1));
        task.setDate(cursor.getString(2));
        task.setDone(Boolean.valueOf(cursor.getString(3)));
        tasks.add(task);
    }
    while(cursor.moveToFirst());
}

return tasks;

But it is not returning any record.

Make sure you have data inside your database, especially data matching that date. — Kurt Van den Branden
– Kurt Van den Branden, Commented Jan 27, 2016 at 6:48
Notice that Sql dates will not be equal if the time is different (i.e the same date but different times). If you want to just compare by date without time, you should cast to date only — user586399
– user586399, Commented Jan 27, 2016 at 6:51

Vishwa · Accepted Answer · 2016-01-27 07:27:48Z

1

To get datas from sqlite database based on Date.You need to use standard format of date while inserting data and retrieving data based on date.The standard date foramt is YYYY-MM-DD. For more info:https://www.sqlite.org/lang_datefunc.html

try this..

String date = "2014-04-22";

instead of

String date = "04-22-2014";

where while saving date in sqlite you need to format the as above mentioned.

Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").

REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC. Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

edited Jan 27, 2016 at 7:27

answered Jan 27, 2016 at 7:05

Vishwa

1,1121 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Wai Yan Hein Over a year ago

Yes I inserted using that format and retrieving using that format.

Vishwa Over a year ago

use TEXT instead of using DATE .try this.... "CREATE TABLE "+TABLE_NAME+" ("+COLUMN_ID+" INTEGER PRIMARY KEY AUTOINCREMENT,"+COLUMN_DESCRIPTION+" TEXT,"+ COLUMN_DATE+" TEXT,"+COLUMN_DONE+" BOOLEAN)";

Vishwa Over a year ago

for supported datatypes in sqlite refer this...sqlite.org/datatype3.html

Wai Yan Hein Over a year ago

So it means. Whenever I store date, it is recommanded to use TEXT only ?

Ajinkya · Accepted Answer · 2016-01-27 06:48:27Z

0

try this

String query = "SELECT * FROM "+TABLE_NAME + " where "+ COLUMN_DATE + " = "+ "'"+date+"'";

answered Jan 27, 2016 at 6:48

Ajinkya

1,0777 silver badges18 bronze badges

3 Comments

Ajinkya Over a year ago

have you checked data in db is it there?

Wai Yan Hein Over a year ago

Yes data is inside the db

Ajinkya Over a year ago

refer this link stackoverflow.com/questions/2309227/…

Collectives™ on Stack Overflow

Cannot retrieve database record using WHERE on DATE column of SQLite in Android

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related