Android Question Sqlite order by date.

jvrh_1

Active Member
Licensed User
I have a sqlite database with a field called as fecha.
I want to get data ordered by fecha (first the newest records and at the last, the oldest ones).
My fecha field is the type text (because in sqlite there is not type date) with this format yyyy-MM-dd. Example: 2020-01-03.
I have tried this one:

SELECT * FROM table1 order by date(fecha,"yyyy-MM-dd") ASC
SELECT * FROM table1 order by date(fecha,"yyyy-mm-dd") ASC
SELECT * FROM table1 order by date(fecha,"yyyy-MM-dd") ASC
SELECT * FROM table1 order by date(fecha,"yyyy-mm-dd") DESC

SELECT * FROM table1 order by substr (fecha,7,2) || substr(fecha,4,2) || substr(fecha,1,2) ASC
SELECT * FROM table1 order by substr (fecha,7,2) || substr(fecha,4,2) || substr(fecha,1,2) DESC

I have written before this: DateTime.DateFormat=("yyyy-MM-dd") and DateTime.DateFormat=("yyyy-mm-dd"). I think that is not neccessary but I have tried it too.

In all of cases I get the same order in my reading.

Someone knows how can I order my records by date in sqlite?.
Thanks. I have worked in it since two days ago. Please help.
 

jvrh_1

Active Member
Licensed User
Sorry again.
I have found the solution for my problem. Here it is:
Remember: My date format is yyyy-MM-dd

SELECT * FROM table1 order by substr (fecha,0,4) || substr(fecha,5,7) || substr(fecha,8,10) DESC

I hope that it will be usefull for somebody. Thanks for all.
 
Upvote 0

asales

Expert
Licensed User
Longtime User
There is no DATE type in SQLite, but you can create a field with this type and save the date in format yyyy-mm-dd.

This works fine to me:
B4X:
SELECT fecha FROM table1 order by fecha ASC
2019-07-29
2019-07-30
2019-07-31
2019-08-01

SELECT fecha FROM table1 order by fecha DESC
2020-07-29
2020-04-14
2020-03-14
2020-02-18
2020-01-26
 
Upvote 0

jvrh_1

Active Member
Licensed User
There is no DATE type in SQLite, but you can create a field with this type and save the date in format yyyy-mm-dd.

This works fine to me:
B4X:
SELECT fecha FROM table1 order by fecha ASC
2019-07-29
2019-07-30
2019-07-31
2019-08-01

SELECT fecha FROM table1 order by fecha DESC
2020-07-29
2020-04-14
2020-03-14
2020-02-18
2020-01-26

Yes, I did it, but I could order by this field because it was a type text. I solved it with this sentence:
SELECT * FROM table1 order by substr (fecha,0,4) || substr(fecha,5,7) || substr(fecha,8,10) DESC
Thanks a lot.
 
Upvote 0

asales

Expert
Licensed User
Longtime User
I changed the type of the date field to text and the SQL code still works (sorted the date correctly in desc or asc order). Don't need to separate the field.
 
Upvote 0

jvrh_1

Active Member
Licensed User
I changed the type of the date field to text and the SQL code still works (sorted the date correctly in desc or asc order). Don't need to separate the field.
It did not work for me. I do not know why, but there are more people like me. There are more threats above It. Your solution is perfect but It did not work for me.
 
Upvote 0

derez

Expert
Licensed User
Longtime User
Simpler to keep the date in the DB as long (INTEGER) and translate it to DateTime format in the reading application.
 
Upvote 0
Top