Order table by date stored in text column

Question

I have and app and storing some data on SQLite. And i have a column named date and storing string data format like:

30.12.2018 - some data
01.01.2019 - some data
31.12.2018 - some data
02.01.2019 - some data

but when i want to SELECT * FROM table ORDER BY date, this doesn't work correctly. Can I order this table according to correct date order without changing date format?

E.g. when I use SELECT * FROM mytable ORDER BY date the desired result:

02.01.2019 - some data
01.01.2019 - some data
31.12.2018 - some data
30.12.2018 - some data

does this help https://stackoverflow.com/questions/8948435/how-do-i-order-my-sqlite-database-in-descending-order-for-an-android-app or are you using Room? — joao86, Jan 12 '19 at 12:02
The probable cause for not ordering right is that since it is a String then the "some data" part is influencing the sorting. You should remove that part from the field (creating a new column possibly) and then it should work. — joao86, Jan 12 '19 at 12:06

score 1 · Accepted Answer · answered Jan 12 '19 at 12:13

You must change the format of this column in order to avoid situations
where you need statements like this:

select * from mytable 
order by 
substr(date, 7, 4) || substr(date, 4, 2) || substr(date, 1, 2) || substr(date, instr(date, '-')) desc

This part:

substr(date, 7, 4) || substr(date, 4, 2) || substr(date, 1, 2)

rearranges the date to YYYYMMDD so it's comparable and can be sorted.

Order table by date stored in text column

1 Answers1