sorting data by date ?

Question

Hi,

I am sorting my database depending on date. The date field in SQLite is nothing but a string, stored in the form of dd-mm-yyyy. How do i carry out the sorting.

My idea is to create a dummy table.Convert the date in a format yyyymmdd. Then sort it simply using sort by date column.Then again drop the table.

Is there an efficient or a better way ?

Answer 1

You should recreate your database to store data as ISO date yyyy-mm-dd (as recommended) then the sorting will be fine in SQLite.

Otherwise from the above, you can always substring fields from this field, and ordey by them, but that is so oldschool. Too bad on Android you can't have user defined functions.

Answer 2

The better way is to have a Date datatype in your database. In that way you can easily:

SELECT * FROM table ORDER BY dateColumn;

or

SELECT * FROM table ORDER BY dateColumn DESC;

if you want it in the other order.

It will be easier for you if you just make it a date data type.

edit
Wrong link. Thanks for comment, here's the correct link for datatypes: http://www.sqlite.org/datatype3.html
edit