无法从最新的SQLite获取数据 [英] Can't get data from date to date SQLite

问题描述

我在android中有一个SQLite数据库，我想从表"Transactions"中选择仅从date1到date2的数据.

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

代码:

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

这是数据库数据

查询结果:

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()
    }

更改格式后的新数据库: DataBaseUpdatedDateFormat

new database after I changed format: DataBaseUpdatedDateFormat

数据库查询:这是对我有用的查询

推荐答案

这将无法正常工作，因为您的数据库数据格式不正确.您将需要使用01/09/2020格式，但是您需要使用1/9/2020格式.表示您的substr索引不是恒定的.

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.

您可以尝试使用示例中提供的语法，因为Date()需要语法 DATE('2020-10-31')

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

最终代码:

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')

当数据库中的数据格式也更改为 yyyy-mm-dd 时，您只需调用

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')

这篇关于无法从最新的SQLite获取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！