CODEWITHSUNDEEP
javaandroidsqlite
ItsRedwan
ItsRedwan

Reputation: 173

How to Select SQLIte columns based on date from Date Column

I have a SQLite Database where I am getting data from all columns based on a given date eg. 30 Sep 2018. Where in COL2 I am saving dates in the same format.

Cursor inc_con_by_dat(String dat) {
    SQLiteDatabase db = this.getWritableDatabase();

    String que = "SELECT * FROM " + TABLE_NAME +
            " WHERE " + COL2 + " = '" + dat + "'";
    return db.rawQuery(que, null);
}

But now the COL2 Doesn't have the date format like 30 Sep 2018 anymore. cause I have decided to include data entry time in COL2 as well.

So I am saving COL2 in "EEE MMM dd HH:mm:ss Z yyyy" this format. But I am willing to change my "EEE MMM dd HH:mm:ss Z yyyy" format to anything that includes date and time. But I want to select columns only supplying like this example format "30 Sep 2018" or yyyy/mm/dd So can anybody help me. or I just have to make another column for the time entry as well.

Upvotes: 0

Views: 557

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

So I am saving COL2 in "EEE MMM dd HH:mm:ss Z yyyy" this format

Stop doing that. SQLite does not actually have a date column type, which means that your date information is being stored as literal text. Rather than trying to give you a workaround, I will just recommend that you always store your dates and timestamps in SQLite using an ISO format, something like:

YYYY-MM-DD HH:MI:SS

If you take this advice, then you would easily be able to compare another ISO date or timestamp against any record in your table. For example, if you wanted to find all records having a timestamp which fell on 2018-09-30, you could use this query:

SELECT *
FROM yourTable
WHERE ts >= '2018-09-30' AND ts < '2018-10-01';

Upvotes: 2

Related Questions