Schakalaka Active Member Licensed User Longtime User Apr 4, 2023 #1 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: B4X: Copy 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 Attachments MieFinanze.zip MieFinanze.zip 88.6 KB · Views: 243
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: B4X: Copy 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 Expert Licensed User Longtime User Apr 4, 2023 #2 Which is the date format that you use in the sqlite? I use this format and works fine: yyyy-MM-dd Upvote 0
Schakalaka Active Member Licensed User Longtime User Apr 4, 2023 #3 DateTime.DateFormat= "dd/MM/yyyy" B4X: Copy DateTime.DateFormat= "dd/MM/yyyy" but on database it is type TEXT... Upvote 0
DateTime.DateFormat= "dd/MM/yyyy" B4X: Copy DateTime.DateFormat= "dd/MM/yyyy" but on database it is type TEXT...
DonManfred Expert Licensed User Longtime User Apr 4, 2023 #4 Schakalaka said: but on database it is type TEXT... Click to expand... 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.... Upvote 0
Schakalaka said: but on database it is type TEXT... Click to expand... 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....
M Mahares Expert Licensed User Longtime User Apr 4, 2023 #5 Schakalaka said: DateTime.DateFormat= "dd/MM/yyyy" Click to expand... 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: Code: Copy 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 Last edited: Apr 5, 2023 Upvote 0
Schakalaka said: DateTime.DateFormat= "dd/MM/yyyy" Click to expand... 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: Code: Copy 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
