CODEWITHSUNDEEP
androidsqlite
Jonesie
Jonesie

Reputation: 132

How to delete rows in Android SQLite table where date is prior to a specified date (where date is in the format MM/DD/YYYY)

I have an Android SQLite table and need to delete rows from the table where the date is prior to 05/01/2019. I know this is simple if the format is YYYY/MM/DD however the format is this MM/DD/YYYY.

The db contains this data: "05/03/2019" "05/03/2019" "01/15/2020" "06/15/2020"

When I do this:

String whereClause=" created_date <= '05/01/2019'";
getContext().getContentResolver().delete(MyTable.CONTENT_URI, whereClause, null);

It will delete the row that contains: "01/15/2020, because, 01/15/2020 is less than 05/01/2019.

I'm making the assumption that I'll need to fetch all rows then iterate through them and convert the MM/DD/YYYY to YYYY/DD/MM, then delete based on the date comparison.

Upvotes: 0

Views: 89

Answers (1)

Andreas
Andreas

Reputation: 159135

Refactor the string value inline to make it ordered, i.e. refactor the string value from format MM/DD/YYYY to format YYYY/MM/DD using substr() and || string concatenation:

String whereClause = " substr(created_date,7,4) || '/' || substr(created_date,1,5) <= '2019/05/01'";

Upvotes: 1

Related Questions