Order table by date stored in text column

21
January 12, 2019, at 8:10 PM

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

Answer 1

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.

READ ALSO
How to show snackbar throughout the Application?

How to show snackbar throughout the Application?

I have more than 3 Activity but let's just take three of them, BaseActivity, Splashscreen, MainActivitySplashscreen and Mainactivity extends BaseActivity

16
Androidx Preferences not incuding RingtonePreference

Androidx Preferences not incuding RingtonePreference

I'm doing the settings activity for my app via code since the xml editor wont find any preferences but PreferenceScreenVia code everything is working but I can not find the RingtonePreferences from Androidx, the only one I find is the one from android

41
How to access dynamic layout in adapter?

How to access dynamic layout in adapter?

I have one adapter in which I have one button, I will click on that button and opening alert dialogue with Yes and No buttonsNow if user will click on that button I am creating one dynamic layout with linear layout and converting it to Image and share it with Installed...

23