Sorting Dates In Sqlite Database?

I have developed an App ..For that I have a database and it has many tables. One of the table has date column. My question is I have to sort the dates and have to pick oldest one

Solution 1:

In sqlite does not really have a date type. You can store dates as strings using one of their predefined formats see http://www.sqlite.org/lang_datefunc.html for more information.

A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MMYYYY-MM-DDTHH:MM:SSYYYY-MM-DDTHH:MM:SS.SSSHH:MMHH:MM:SSHH:MM:SS.SSS
now
DDDDDDDDDD

You need to store them in YYYY-MM-DD then you can sort them order by asc limit 1 to get the oldest date. So instead of

Column_Date
------------
Nov-07-2012
Nov-21-2012
Nov-25-2012
Oct-25-2012
Oct-24-2102

You will have to store them like this instead

Column_Date------------2012-11-072012-11-212012-11-252012-10-252012-10-24

Finally you read the rows if any

CursoroldestDateCursor= db.query("DateTableName", null, null, null, null, null, "date_column ASC LIMIT 1");
if (oldestDateCursor.moveToFirst())
{
    Stringdate= oldestDateCursor.getColumnName(oldestDateCursor.getColumnIndex("date_column"));
}
oldestDateCursor.close();

Solution 2:

You can change how it is being stored. Instead of one column, have 3: month , day , year ;

This allows more flexibilityand you can sort it more easily. Since it is numbers month(1-12) instead of strings, it will be more easily sorted.

SELECTmonth,day,yearFROM tables
ORDERBYmonth,day,yearDESC;

Solution 3:

Firstly, you cannot store the type "date" in sqlite. I'm sure that there are plenty of equally laudable ways of achieving this but here's how i personally do what you're asking. I convert all my dates to this string format: new SimpleDateFormat("yyyyMMdd"); I put that into my database. It's convenient and i hope for self-evident reasons. And when i want to return my dates, i simply use an order in the query like "date DESC". then i reconvert it to whatever format i needed them in.