Help ordering a result by date in SQLite

Question

Is there any way in SQLite to ORDER BY a date, and have result be ordered by time rather than alphabetically?

For example:

SELECT * FROM details GROUP BY date;

John    |     Smith     |     April 01, 2011
John    |     Smith     |     April 03, 2011
John    |     Smith     |     April 04, 2011
John    |     Smith     |     March 25, 2011

March should come before April.

I'm guessing that the answer here is to store my dates as long timestamps, however I wasn't sure if it could be done more easily with SQLite.

Thanks!

Answer 1

There isn't a built-in DATE type in SQLite (as exists in some other database management systems), but it does have a nice complement of date and time functions: http://www.sqlite.org/lang_datefunc.html

You can use date("April 01, 2011") to get back an ISO-8601 date (e.g., 2011-04-01).

This format has the advantages of both being a readable string and being sortable. 2011-03-25 naturally comes before 2011-04-01 by standard string comparison rules, so there's no special operation required.

So, store your dates in that format, and get that format using the date() function (or other relevant function).

Answer 2

you can convert date to an actual date in order to compare it.

try to add order by julianday(date) to the end of the query

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions