Android Question Clv and order data from sql - not that easy

[Schakalaka]

Hello,
I have create a small sqlite database for manag emy money usage.

i add data inside a table, and I view it inside a clv.
My problem, is that if I add a field with data before, the order is not correct by DATE.

I use this query for oder data by DATE:

rs = SQL1.ExecQuery2($"SELECT idmov, Data, Tipo, Destinatario,Descrizione,Conto,Importo FROM tbl_movimenti BY ORDER BY DATA ASC"$, Null)

I attach the project. the database have no data.
try to add a value for today, then one with date before, and then with date 1/1. It not order by date...

feel free to make impovements

Thank you

 

[asales]

Which is the date format that you use in the sqlite?
I use this format and works fine:
yyyy-MM-dd

 

[Schakalaka]

DateTime.DateFormat= "dd/MM/yyyy"

DateTime.DateFormat= "dd/MM/yyyy"

but on database it is type TEXT...

 

[DonManfred]

but on database it is type TEXT...

Data with dd/MM/yyyy is sorted by monthday, month and then by year in a TEXT-Field...

Store the date as yyyy-MM-dd in the database.

Best into a DATE field. Or just store the ticks (long-Value) and order by it then....

 

[Mahares]

DateTime.DateFormat= "dd/MM/yyyy"

If you must have your date as TEXT in this format: dd/MM/yyyy in your database, you can still sort the records by date , and get the correct order, but you have to use the SUBSTR function of SQLite like this:

ORDER BY (substr(Data,7,4) || substr(Data,4,2) || substr(Data,1,2)) Asc

The sort will be executed based on: yyyyMMdd and you will be good to go. You can sort either ASC or DESC