How to Order Room results by Date as String

Question

I have a room database. I have a column for date and it's saved as string.

I used this query for sort my column :

@Query("SELECT * FROM session WHERE class_id = :classId ORDER BY session_date ASC")
List<SessionEntry> getAllSessions(int classId);

Result :

but i want to sort like this :

Is there any way I could order by Date as String without modifying the database structure ?

Use timestamp to save the date cause it will easy to sort it. — Kaushik Burkule, Oct 01 '19 at 09:06
Is possible to change the format of your date column into yyyy-MM-dd i.e. with the month and day always on two digits ? — matdev, Oct 01 '19 at 09:08
Check [this](https://medium.com/androiddevelopers/room-time-2b4cf9672b98). — nfl-x, Oct 01 '19 at 09:15
Please try this: @Query("SELECT * FROM session WHERE class_id = :classId ORDER BY (SUBSTRING_INDEX(SUBSTRING_INDEX(session_date, '/', 2), '/', 1)) ASC") — Dhaval Solanki, Oct 01 '19 at 09:32
There is a problem with the query: [SQLITE_ERROR] SQL error or missing database (no such function: SUBSTRING_INDEX) @Dhaval Solanki — Nima Khalili, Oct 01 '19 at 09:46

score 1 · Answer 1 · answered Oct 01 '19 at 09:11

1

in my case it is working as I have like float type formate :

  @Query("SELECT * FROM growthlogdata WHERE babyid = :childid Order By CAST(dateGrowth AS FLOAT) ASC")
List<SessionEntry> getAllSessions(int classId);

answered Oct 01 '19 at 09:11

bugfreerammohan

1,471
1
7
22

it is yyyy-mm-dd – Nima Khalili Oct 01 '19 at 09:20
you need to take dates in float formate and save it and you can display in yyyy/mm/dd – bugfreerammohan Oct 01 '19 at 09:21
you cant short dates in String formate as you are doing I had the same issue as you – bugfreerammohan Oct 01 '19 at 09:22

score 1 · Answer 2 · answered Oct 01 '19 at 13:51

First answer, don’t store dates as strings, use a proper date datatype.

However, if I understand correctly that your SQLite database hasn’t got a date datatype, @matdev is correct: The best solution is to change the format into yyyy-mm-dd. This conforms with ISO 8601 and will sort chronologically.

score 1 · Accepted Answer · answered Oct 02 '19 at 14:48

I found a solution but it's not best.

This way is for when you can't change your Date Column from String to another type

List<SessionEntry> sessionEntries = mDb.sessionDao().getAllSessions(classId);
Collections.sort(sessionEntries, comparing(SessionEntry::convertStringToDate));

Session Entry :

public class SessionEntry {
    .
    .
    .
    public Date convertStringToDate() {
        try {
            return new SimpleDateFormat("yyyy/MM/dd").parse(getSessionDate());
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return null;
    }
}

This helped me as well. I have date column as string. – blueware Sep 15 '20 at 07:00 — blueware, Sep 15 '20 at 07:00

How to Order Room results by Date as String

3 Answers3

Linked