How to get data between two dates from sqlite database which is in dd/mm/yyyy format

Question

I'm building an app which select rows between to dates which is in dd/mm/yyyy format and count the row having status pending, signup and rejected. I have done some work on it, but its not working. I stored date as TEXT in database. I pasted code below.

public void showMonthlyPopUp(View view) {
    weeklyDialog.setContentView(R.layout.pop_up_all_list);
    TextView nameTextView = weeklyDialog.findViewById(R.id.textView);
    nameTextView.setText("MONTHLY");
    TextView pendingTextView = weeklyDialog.findViewById(R.id.textView6);
    TextView signUpTextView = weeklyDialog.findViewById(R.id.textView3);
    TextView rejectedTextView = weeklyDialog.findViewById(R.id.textView7);
    Button shareButton = weeklyDialog.findViewById(R.id.button_share);

    String[] projection = {
            InfoContract.InfoEntry._ID,
            InfoContract.InfoEntry.COLUMN_STATUS,
            InfoContract.InfoEntry.COLUMN_DATE
    };

    Calendar calendar = Calendar.getInstance();
    String strDate = calendar.get(Calendar.MONTH) + "/" + calendar.get(Calendar.YEAR);
    int dayInt = calendar.get(Calendar.DAY_OF_MONTH);

    String[] selectionArgs = new String[dayInt];
    for (int i = 1; i <= dayInt; i++) {
        selectionArgs[i - 1] = i + "/" + strDate;
    }

    String selection = InfoContract.InfoEntry.COLUMN_DATE + " =?";
    for (int i = 1; i < dayInt; i++) {
        selection += " OR " + InfoContract.InfoEntry.COLUMN_DATE + " =?";
    }
    Cursor cursor = this.getContentResolver().query(InfoContract.InfoEntry.CONTENT_URI, projection, selection, selectionArgs, null);
    int pending = 0;
    int signUp = 0;
    int rejected = 0;
    while (cursor.moveToNext()) {
        int statusColumnIndex = cursor.getColumnIndex(InfoContract.InfoEntry.COLUMN_STATUS);
        int status = cursor.getInt(statusColumnIndex);
        if (status == InfoContract.InfoEntry.STATUS_SIGN_UP) signUp = signUp + 1;
        else if (status == InfoContract.InfoEntry.STATUS_REJECTED) rejected++;
        else pending++;
    }
    cursor.close();
    pendingTextView.setText("" + pending);
    signUpTextView.setText("" + signUp);
    rejectedTextView.setText("" + rejected);
    weeklyDialog.show();
    final int finalPending = pending;
    final int finalSignUp = signUp;
    final int finalRejected = rejected;
    shareButton.setOnClickListener(new View.OnClickListener() {
        @Override
        public void onClick(View view) {
            shareData(finalPending, finalSignUp, finalRejected, "Monthly Details: ");

        }
    });

}

Answer 1

Life will be quite hard using the format dd/mm/yyyy as it's not readily usable by the most obvious SELECT using a BETWEEN clause as part of the WHERE clause, even harder when dd/mm/yyyy is often with single characters for values that are less than 10 (e.g. 1/1/2019 instead of 10/10/2019).

Consider mytable created and loaded using :-

DROP TABLE IF EXISTS mytable;
CREATE TABLE IF NOT EXISTS mytable (mydatecolumn TEXT, myothercolumn TEXT DEFAULT 'BLAH');
INSERT INTO mytable (mydatecolumn) 
    VALUES 
        ('01/01/2019'),('1/1/2019'),('01/1/2019'),('1/01/2019'),
        ('10/1/2019'),('10/10/2019'),('1/10/2019'),('01/02/2019'),
        ('1/3/2019'),('01/1/2019'),('14/01/2019'),('10/1/2019'),
        ('10/10/2020'),('1/10/2018')
; 

Which looks like :-

The query to transpose values and to then select a date range could be :-

-- An example that would hanlde dd/mm/yyyy where dd and mm could be either 1 or 2 characters
WITH 

    -- First CTE gets the day and the rest of the date
    ctedaypart AS (
        SELECT 
            rowid AS daypartid, 
            substr(mydatecolumn,1,instr(mydatecolumn,'/')-1) AS day_part, 
            substr(mydatecolumn,instr(mydatecolumn,'/')+1) AS rest_after_day 
        FROM mytable
    ),
    
    -- Second CTE gets the month and the rest of the date
    ctemonthpart AS (
        SELECT 
            daypartid AS monthpartid,
            substr(rest_after_day,1,instr(rest_after_day,'/')-1) AS month_part, 
            substr(rest_after_day,instr(rest_after_day,'/')+1) AS year 
        FROM ctedaypart
    ),

    -- Third CTE expands the day and month the have a leading 0 id less than 10 and joins the parts to form YYYY-MM-DD 
    expandedparts AS (
        SELECT
            *,
            mytable.rowid AS expandedpartsid,
          year||'-'||
            CASE WHEN length(month_part) = 1 THEN '0'||month_part ELSE month_part END ||'-'||
            CASE WHEN length(day_part) = 1 THEN '0'||day_part ELSE day_part END AS date_in_sqlite_format
        FROM mytable JOIN ctedaypart ON mytable.rowid = daypartid  JOIN ctemonthpart ON daypartid = monthpartid)
        
SELECT mytable.* FROM mytable JOIN expandedparts ON mytable.rowid = expandedpartsid WHERE (date_in_sqlite_format) BETWEEN ('2019-01-01') AND ('2019-03-31');

The above results in 10 of the 14 rows being selected as per :-

---

However

If the date is saved in the database in a recognised format e.g. YYYY-MM-DD then the above could simply be :-

SELECT * FROM mytable WHERE (mydatecolumn) BETWEEN ('2019-01-01') AND ('2019-03-31');

---

As such it is suggested that you adopt the use of a recognised format for dates when interacting with the database :-

Time Strings
A time string can be in any of the following formats:

YYYY-MM-DD
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDDDDDDDD

SQL As Understood By SQLite - Date And Time Functions

The alternative is to use or adapt the complex query above or use one that is similar.