Reputation: 114
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)
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
Upvotes: 0
Views: 129
Reputation: 898
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')
Upvotes: 1