How Can I Get Sqlite To Sort By Date Properly Using Yyyy-mm-dd Format?

I'm making an app that needs to sort a Sqlite table of weeks by date and display just the date and the total hours worked that week in a listview. My activity extends listview and

Solution 1:

Use leading zeros for the month and day.

Solution 2:

SQLite does not have built-in date fields; you have to use TEXT (textual representation), INTEGER (unix time) or REAL. See: Datatypes in SQLite.

You can use leading zeroes (as dan suggested) or better yet, save them as UNIX timestamps in INTEGER fields. You can convert these back to human-readable dates using SQLite's date and time functions or Java's (or whatever you are using).

Solution 3:

I have had to do similar sorting. I ended up storing dates as REAL values in SQLite as the number of milliseconds since Epoch Jan 1 1970 00:00 GMT. You can then simply sort these numerically (larger number occurred later chronologically). You can then convert to strings using the SimpleDateFormat. Also allows you to do great things with time elements which helps-- http://developer.android.com/reference/java/text/SimpleDateFormat.html