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.

Answer 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.

Answer 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

Answer 3

According to the documentation the following should work

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

Answer 4

try:

SELECT * FROM data ORDER BY to_date(date_column)

probably this might solve you problem as it is going for string comparison rather than date comparison so

01/03/2013 appears smaller than 28/02/2013 or 27/02/2013

thus output is :

01/03/2013, 27/02/2013, 28/02/2013

Answer 5

This query worked for me filter dates

SELECT inst.*,
       Substr(columnname, 4, 2)  AS newdate,
       Substr(columnname, 0, 3)  AS newday,
       Substr(columnname, 12, 5) AS newtime
FROM   table_name AS inst
WHERE  child_id = id
ORDER  BY newdate DESC, newday DESC, newtime DESC