Sqlite order by date (yyyy/mm/dd format)

Question

How to sort the table based on date?

I have a column called: mydate. The date is stored in this format: 29/1/2014 (dd/mm/yyyy).

Now i need sqlite to sort the table descending based on the date.

The problem is: How can i do this?

I already tried this but this does not sort anything:

SELECT * FROM my_table ORDER BY date(mydate);

score 0 · Accepted Answer · edited May 23 '17 at 11:56

When stored in (dd/mm/yy) it will not be able to differentiate in minutes/hours. Is there no sorting occurring at all? Can you show us some sample data and output?

I've had this issue before, and I had to add in actual time rather that only date.

If that is the issue, the following link will help: SQLite Order By Date

It's basically what you have, but you need to create your column with DATETIME and need to insert with the following format:

'2007-01-01 10:00:00'

If not, then please post the additional data and I will try to help.

score 0 · Answer 2 · answered Apr 10 '18 at 13:16

If you applied order by on myDate date it will sort by mm(day of month) not by date so just change date format YYYY-mm-dd which is support by SQLite. Modify your query as follow.

SELECT * FROM yourTableName order by (substr(mydate, 7, 4) || '-' || substr(mydate, 4, 2) || '-' || substr(mydate, 1, 2));

Sqlite order by date (yyyy/mm/dd format)

2 Answers2