CODEWITHSUNDEEP
sqlitesortingdate
Jennifer Dunne
Jennifer Dunne

Reputation: 3

SQLite date function doesn't appear in SELECT results

I've just started using SQLite, instead of SQL Server, and it doesn't seem to want to do ORDER BY, MAX() or MIN() on dates.

The Survey_Date column is a text field, so ordering it sorts it from January to December, instead of by the year. If I include date(Survey_Date) in my SELECT statement, it will sort the data by year -- but I can't override the DESC sort, and it doesn't actually display the date.

SELECT Survey_Date FROM Surveys WHERE Loc_ID = 32 ORDER BY Survey_Date;

yields results like:

01/10/2009
01/20/2013
02/05/2010
...

SELECT date(Survey_Date), Survey_Date FROM Surveys WHERE Loc_ID = 32 ORDER BY 1 ASC;

yields results like:

|01/20/2013
|02/05/2010
|01/10/2009
...

It's clearly sorting on the date correctly now, but it doesn't display the formatted date, and doesn't recognize the ASC command.

Can anyone explain what it's doing?

Upvotes: 0

Views: 62

Answers (1)

Jan Hudec
Jan Hudec

Reputation: 76246

As already explained at least million times over this site, SQLite does not have date/timestamp type.

So if you want to sort by date, you must store them in format that sorts correctly either numerically, or lexicographically (asciibetically/unicodebetically).

The recommended format is ISO-8601, that is yyyy-mm-dd (yyyy-mm-ddTHH:MM:SS if you want a time too). This is what SQLite has some utility functions to work with, too.

Other possible formats are numeric, either number of seconds since some specific point, e.g. Unix time or number of days, e.g. (Modified) Julian day.

01/10/2009
01/20/2013
02/05/2010

They are strings, so this is correctly sorted.

SELECT date(Survey_Date), Survey_Date FROM Surveys WHERE Loc_ID = 32 ORDER BY 1 ASC;

  1. The date function expects ISO-8601 format. It does not understand what you are giving it and returns NULL. Fully expected.
  2. Sorting by all NULL values effectively does nothing. The rows came out sorted by accident.

Upvotes: 3

Related Questions