0

I have an SQLite database in android and I want to Select From a table "Transactions" only data from date1 till date2.

Code:

SELECT * FROM Transactions WHERE DATE(substr(transactiondate,7,4) ||substr(transactiondate,4,2) ||substr(transactiondate,1,2)) BETWEEN DATE(20201030) AND DATE(20201031)

Here is the database data

result of query:

Execution finished without errors.
Result: 0 rows returned in 13ms

Update: Here is how I changed my data to "year-month-day" format:

val c = Calendar.getInstance()
    val year = c.get(Calendar.YEAR)
    val month = c.get(Calendar.MONTH)
    val day = c.get(Calendar.DAY_OF_MONTH)
    val correctMonthDefault = month+1
    if(day<10 && correctMonthDefault>9){
    tv_add_date.setText("" + year + "-" + correctMonthDefault +"-"+ "0"+ day )}
    else if(correctMonthDefault<10 && day>9)
    {
        tv_add_date.setText("" + year + "-0" + correctMonthDefault +"-"+ "0"+ day )}
    else if(day<10 && correctMonthDefault<10){
        tv_add_date.setText("" + year + "-0" + correctMonthDefault +"-0" + day )}
    else {
        tv_add_date.setText("" + year + "-" + correctMonthDefault +"-" + day )}

    btnCalendar.setOnClickListener {
        val dpd = DatePickerDialog(this, DatePickerDialog.OnDateSetListener { datePicker, mYear, mMonth, mDay ->
            var correctMonth = mMonth + 1
            if(mDay<10 && correctMonth>9){
                tv_add_date.setText(""+ mYear +"-" + correctMonth + "-0" + mDay)
            }
            else if(mDay>10 && correctMonth<10){
                tv_add_date.setText(""+ mYear +"-0" + correctMonth + "-" + mDay)
            } else if(mDay<10 && correctMonth<10){
                tv_add_date.setText(""+ mYear +"-0" + correctMonth + "-0" + mDay)
            }else{
                tv_add_date.setText(""+ mYear +"-" + correctMonth + "-" + mDay)
            }}, year, month, day)
        dpd.show()
    }

new database after I changed format: DataBaseUpdatedDateFormat

DataBase query: Here is the query that worked for me

Andrei Meriacre
  • 114
  • 1
  • 7

1 Answers1

1

This will not work properly, since your database data is not properly formatted. You would need a 01/09/2020 format but you have 1/9/2020 format. Meaning your substr indices are not constant.

Can you change the way you enter data to the database? Then it will work.

You can try to use the syntax provided in the example as Date() needs the syntax DATE('2020-10-31')

WHERE date BETWEEN '2020-10-30' AND '2020-10-31'

https://www.techonthenet.com/sqlite/between.php

Final code:

SELECT * FROM Transactions WHERE DATE(substr(transactiondate,7,4) || '-' || substr(transactiondate,4,2) || '-' || substr(transactiondate,1,2)) BETWEEN DATE('2020-10-30') AND DATE('2020-10-31')

When your dataformat in the database is also changed to yyyy-mm-dd you can simply call

SELECT * FROM Transactions WHERE transactiondate BETWEEN DATE('2020-10-30') AND DATE('2020-10-31')
Tobi
  • 858
  • 7
  • 15
  • Thank you a lot! First I formatted my dates with '0' before the month and day less than 10, and it didn't work. After that, I changed it to the format you mentioned "year-month-day" and it still didn't work. And with help of the link, you provided I changed my query from the example to: SELECT * FROM Transactions WHERE transactiondate BETWEEN DATE('2020-10-30') AND DATE('2020-10-31') And it worked! Thank you very much for your help. – Andrei Meriacre Nov 05 '20 at 12:39
  • @AndreiMeriacre glad I could help, consider marking this as the correct answer to help others finding the way to it – Tobi Nov 05 '20 at 12:58