Sort lists by date?

I have a question. In my ap开发者_StackOverflow中文版p I have saved in my database some lists. Each list has asociated a date in this format 6-June-2011. How can I order these lists by date? I wrote I function like that :

public Cursor getAll(){

    return (mDb.rawQuery("SELECT _id, Title, Shop , Data , Budget_allocated ," +
            " Budget_spent FROM Lists ORDER BY Data",null));
}

but it doesn't work fine. I think it compare only the day. For example, if I have 31-May-2011 and 6-June-2011, it will say that the first date is after the second date.

It is possible what I am trying to do? Should I modify the date in format like this :6-06-2011?

Thanks..

I don't know anything about android development, but it sounds like your date field is stored as a string rather than a date, is that correct?

If so, you can either:

• Change your table so that field is a date (then it should compare correctly)
• Or store it in a standard format such that the default string comparison sorts dates correctly. Since string comparison sorts first on first character, second on second character, etc, this would be putting the biggest time difference first, ie. "2011-05-31" (include the zeroes).

(You should be able to convert the field by making a new field, copying the data from the old field into the new field in the correct format, and then deleting the old field and renaming the new one, or more simply if you simply want to change the format of the text. You should be able to do this either from code, or with an "update" query, AFAIK.)

Use a date format as follows...yyyy-MM-dd HH:mm:ss. This is guaranteed to be sortable in ascending or descending order.

If your Data column is not of type datetime there may be an issue. Take a look at this related SO question and answers:

SQL ORDER BY date problem