order by not formatted date sqlite

Question

I'm storing Dates as string in the database with this format DD-MM-YYYY.

When I tried to make a select query with an orderby on the date column. I didn't get the expected result.

example of result : 28/02/2013 27/02/2013 01/03/2013

My sql query :

SELECT * FROM data ORDER BY strftime('%s', date_column)

Thank you.

Solution 1

The problem is that you store dates as DD-MM-YYYY strings, which does not only prevent natural ordering of dates as strings, but also parsing them with SQLite's date and time functions. Click the link and scroll down to 'Time Strings' section.

SQLite expects date/time strings in the natural order, most significant digit to least significant, that is, YYYY-MM-DD. You can use string operations to transform your DD-MM-YYYY strings into that form. For instance:

select 
  substr(reversed_date, 7,4) || '-' || 
  substr(reversed_date, 4, 2)|| '-' ||
  substr(reversed_date, 1, 2) as proper_date
  from (
    select '12-03-2000' as reversed_date
  )
;

You can either transform your date column into this format (as @peterm suggests) or just use the value of proper_date for sorting. You don't need to use strftime for that, but date-related functions will work with such values.

Solution 2

IMHO you need to change the format you store dates in from

DD-MM-YYYY

to

YYYY-MM-DD

From docs

Time Strings 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:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
...

Then your original query and this one will work as expected

SELECT * FROM Table1 ORDER BY date(date_column);
SELECT * FROM Table1 ORDER BY strftime('%s', date_column);

Output:

| date_column |
---------------
|  2013-02-27 |
|  2013-02-28 |
|  2013-03-01 |

sqlfiddle

Solution 3

According to the documentation the following should work

SELECT * 
FROM data 
ORDER BY strftime('%Y-%m-%d', date_column)

Comments

[9000]

This does not work. Try select strftime('%Y-%m-%d', '12-03-2000'); yourself.

[Michael Fulton]

sqlite3.OperationalError: no such function: to_date