A Medrano A Medrano - 15 days ago 6
Java Question

Ordering by date not working

I need to filter the information in my database by date, but it doesn't work. The format of the date in my database is as so


Thu Nov 10 00:00:00 GMT+00:00 2016


The database query is:

public Cursor ObterTodasAsReceitasPorUsuario (String cod_usuario){
db = this.getReadableDatabase();
String tp_lancamento = "r";
String sql = String.format("SELECT * FROM %s WHERE %s=? AND %s=? ORDER BY DATE(%s) DESC",
Tabelas.LANCAMENTO, Lancamento.COD_USUARIO, Lancamento.TP_LANCAMENTO, Lancamento.DATA);
String[] selectionArgs = {cod_usuario, tp_lancamento};
Cursor cursor = db.rawQuery(sql, selectionArgs);
Log.d("Despesas", "Despesas");
DatabaseUtils.dumpCursor(cursor);
db.close();
return cursor;
}


This is the result after ordering. Any suggestions on how to solve this?

Answer

I recommend that you format your date like so:

Calendar calendar = Calendar.getInstance();
SimpleDateFormat format = new SimpleDateFormat("dd/MM/YYYY");
String date = format.format(calendar.getTime());

After you have formated the date, insert it into your database. Then, use the following query to sort the dates:

SELECT * FROM Table ORDER BY date(dateColumn)

For more ways of formating the date, see this post.

Comments