android - Why is the ORDER BY not working in a Sqlite query

Question

Sqlite table creation in Android:

// Contacts table name
private static final String TABLE_CHAL = "CD";

// Contacts Table Columns names
private static final String KEY_ID = "id";
private static final String KEY_CHAL = "chal";
private static final String KEY_DATE = "date";

String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CHAL + "("
        + KEY_ID + " INTEGER PRIMARY KEY," + KEY_CHAL + " TEXT,"
        + KEY_DATE + " TEXT)";
db.execSQL(CREATE_CONTACTS_TABLE);

Querying (Order by KEY_DATE):

Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_CHAL + " ORDER BY [" + KEY_DATE + "] ASC", null);

The above doesn't seem to be working.

Do I have to convert the KEY_DATE into date before doing the order.

Answer 1

You can use

SELECT * FROM cd ORDER BY datetime(substr(date, 7, 4) || '-' || substr(date, 4, 2) || '-' || substr(date, 1, 2))