How to return strings that are in ascending date format in SQLite?


I've taken a long and recorded in the database with this format SimpleDateFormat("dd/MM/yyyy") which is a string and now I need to organize the dates. I tried to use this command but it comes in descending order ORDER BY date (data_coleta) and 'ASC' did not work in this case.

asked by anonymous 05.10.2015 / 17:14

2 answers


Record in SimpleDateFormat("yyyy/MM/dd") format. The most significant value - the year - should come before and the least significant - the day - should come in the end. It's basic math to make it grow.

The format that is in the database is independent of any format that will be used in the presentation. If it were another database that has a date type, it would not be in any format that matters to any application.

In the case of SQLite, which does not have this type, you have to take care of this at hand.

Recorded value and presentation format are different things. Record in the format you need in the database and adapt the data whenever you present it.

05.10.2015 / 17:17

SQLite does not sort the dates like the other banks, mysql for example, I also had this problem in my application, but I solved using "compareTo" in my object class.

It may not be the solution you need.

Call Class

public class Call{  

* metodo para ordernar por data      
* @param another Call classe de entrada para comparar    
public int compareTo(Call another) {
    SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");      
    java.util.Date dateComparer, dateActual;
    try {           
        dateActual = sdf.parse(this.getData());             
        dateComparer = sdf.parse(another.getData());

        if (dateActual.before(dateComparer)) {
            return Constants.SUCESS;
        if (dateActual.after(dateComparer)) {
            return Constants.ERROR;
    } catch (ParseException e) {            

    return 0;   

Then I create a list and order:

private List<Call> listCalls(ResultSet resultset){

  List<Call> calls = new ArrayList<Call>();
  do {
     Call item = new Call();
     item._id           = resultset.getString(Call.FIELD1); 
  } while (;  
06.07.2016 / 15:01