Why is Spatialite showing dates in MM-DD-YYYY format?

Question

I have a table with a dates column that is formatted in MM-DD-YYYY format, as shown in SQL SELECT statements. I'm using Qspatilite (QGIS 1.8) and Spatialite GUI.

When I ORDER BY the date column it sorts by MM, then by DD, then finally by YYYY.
In other words, I get rows like this:

01/04/2004
01/24/1987
02/07/1994
03/17/1982
03/27/2009
...
12/29/1997

I understand that SQLITE doesn't enforce data types. Is there an easy way to convert the format to YYYYMMDD (so that I can sort by date) or do I need to manually parse the date myself?

Answer 1

In SQLite, and thus also in SpatiaLite, there's no date type per se. See Datatypes In SQLite.

When a string column is saved in the format "YYYY-MM-DD" then you can apply some date functions to it, such as strftime() to get the date formatted in other ways. However, if you've saved your dates in any other way, they are NOT recognized as dates.

What I would do is create a new date column and insert reformatted date strings into this column from the MM/DD/YYYY strings "correctly" for SQLite. Then sorting will work properly.

So (assuming you have a primary key column id and your current dates column is dates):

ALTER TABLE <your_table> ADD COLUMN new_dates date;
UPDATE <your_table> SET new_dates = 
SELECT SUBSTR(t.dates, 7) || SUBSTR(t.dates, 1, 2) || SUBSTR(t.dates, 4,2)
FROM <your_table> AS t
WHERE t.id = <your_table>.id;

Now, ORDER BY new_dates should work.