How to return strings that are in crescent date format in Sqlite?

Question

I took a long and recorded on the bench in 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 the 'ASC' did not work in this case.

Answer 1

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

The format in the database is independent of any format that will be used in the presentation. If it were another database that has a type date, it wouldn’t 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 it in hand.

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

Answer 2

Sqlite does not sort the dates like other databases, mysql for example, I also had this problem in an application of mine, but I solved using "compareTo" in the class of my object.

Maybe it’s not the solution you need.

Call class

public class Call{  
... 

/**      
* metodo para ordernar por data      
* @param another Call classe de entrada para comparar    
*/   
@Override    
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) {            
        e.printStackTrace();        
    }

    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); 
     calls.add(item);
  } while (resultset.next());  
  ...
  Collections.sort(calls);
}