Deleting Records From Sqlite On The Basis Of Date In Android

I want to delete some records from android's database where two conditions are fulfilled. 1. there's a column with the name as sync_status, it should have a value 'C' and 2. there

Solution 1:

If you absolutely want to keep the date as a string in the form MM-DD-YYYY in your database column, then the only way to do comparison of those dates is to convert them to seconds using SQLite's strftime function. However, in order to do that, you have to restructure the date as YYYY-MM-DD because your current format is not one that can be used as input to the date and time functions of SQLite.

Here is a sample:

DateFormatdateFormat=newSimpleDateFormat("MM-dd-yyyy");
CalendarcalObj= Calendar.getInstance();
StringcurrentDate= dateFormat.format(calObj.getTime());

Stringwhere= KEY_ATTENDANCE_SYN_STATUS + " = ?1 AND "
        + "STRFTIME('%s', SUBSTR(" + KEY_ATTENDANCE_DATE_ONLY + ",7) "
        + "|| '-' || SUBSTR(" + KEY_ATTENDANCE_DATE_ONLY + ",1,5)) < "
        + "STRFTIME('%s', SUBSTR(?2,7) || '-' || SUBSTR(?2,1,5))";
String[] whereArgs = {"C", currentDate};
introws= db.delete(DATABASE_TABLE_DAILY_ATTENDANCE, where, whereArgs);

If you use yyyy-MM-dd when creating currentDate, you can replace the second instance of the ugly substring + concatenation with just STRFTIME('%s', ?2), but you will still need the first substring + concatenation to transform the column values in the table.

If it's not too late for you to change how your database stores the date, make your life easier by either storing as yyyy-MM-dd (to get rid of all the substring + concatenation above), or better yet store the long date and only worry about converting it to and from MM-dd-yyyy at the java layer.

Solution 2:

** EDIT **

Your condition is right it should delete only the rows that meet the condition, just tested it on my SQLite Viewer with some dummy data..

Just be 100% sure that your variable names match the column names and also check the database, if there are some rows which shouldn't be deleted. Maybe there is no entry for today's date and "C" is present in all rows thats why all the records are being deleted.

You can also try the "not so good way":

db.execSQL("delete FROM tableName WHERE KEY_ATTENDANCE_SYN_STATUS = 'C' AND KEY_ATTENDANCE_DATE_ONLY != '"+currentDate+"'");

The above is not a good way as execSQL won't return anything so you won't have anyway to know if it was successful except for checking it yourself.

The above approach is only to test your condition though.