我的SQLite数据库存储发票以及发票编号、创建日期和时间。日期列的类型为TEXT
,即YYYY-mm-dd hh: MM: ss (2018-02-12 03:02:59)
。
如果用户选择2018-02-01
,我希望显示在2018-02-01 00:00:00
和当前日期(2017-02-12 23:59:59
)之间生成的发票编号。有些人说要更改为UNIX纪元时间格式,但目前还不可能。我应如何创建选择查询以获得所需的结果?
我试探着:
SELECT bill_type, bill_amount, bill_date, bill_person_id, _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER BY bill_date ASC)
错误:
> FATAL EXCEPTION: AsyncTask #1
> Process: com.yourbusinessassistant.stocks, PID: 25275
> java.lang.RuntimeException: An error occured while executing doInBackground()
> at android.os.AsyncTask$3.done(AsyncTask.java:304)
> at java.util.concurrent.FutureTask.finishCompletion(FutureTask.java:355)
> at java.util.concurrent.FutureTask.setException(FutureTask.java:222)
> at java.util.concurrent.FutureTask.run(FutureTask.java:242)
> at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1112)
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:587)
> at java.lang.Thread.run(Thread.java:818)
> Caused by: android.database.sqlite.SQLiteException: near "ORDER": syntax error (code 1): , while compiling: SELECT
> bill_type, bill_amount, bill_date, bill_person_id, _id,
> partial_amount, bill_payment_status, bill_payment_date FROM
> bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and
> bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER
> BY bill_date ASC
> #################################################################
> Error Code : 1 (SQLITE_ERROR)
> Caused By : SQL(query) error or missing database.
> (near "ORDER": syntax error (code 1): , while compiling: SELECT bill_type, bill_amount, bill_date, bill_person_id,
> _id, partial_amount, bill_payment_status, bill_payment_date FROM bill_data_details WHERE bill_type = 1002 and bill_person_id = 3 and
> bill_date BETWEEN '2018-02-12 00:00:00 and 2018-02-12 11:59:59' ORDER
> BY bill_date ASC)
> #################################################################
> at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native
> Method)
> at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1093)
> at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:670)
> at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
> at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
> at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
> at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
> at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1454)
> at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1301)
> at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1172)
> at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1340)
> at com.yourbusinessassistant.stocks.database.billsdata.BillDataProvider.query(BillDataProvider.java:47)
> at android.content.ContentProvider.query(ContentProvider.java:1007)
> at android.content.ContentProvider$Transport.query(ContentProvider.java:218)
> at android.content.ContentResolver.query(ContentResolver.java:489)
> at android.content.CursorLoader.loadInBackground(CursorLoader.java:64)
> at android.content.CursorLoader.loadInBackground(CursorLoader.java:42)
> at android.content.AsyncTaskLoader.onLoadInBackground(AsyncTaskLoader.java:312)
> at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:69)
> at android.content.AsyncTaskLoader$LoadTask.doInBackground(AsyncTaskLoader.java:57)
> at android.os.AsyncTask$2.call(AsyncTask.java:292)
> at java.util.concurrent.FutureTask.run(FutureTask.java:237)
3条答案
按热度按时间unftdfkk1#
使用此查询:
这将产生日期-时间介于当前时间和用户输入日期之间所有行
LOCALTIME
用于补偿您时区,因为SQLite datetime
函数使用UTC时区将查询中的错误添加到问题后编辑:
您使用的查询引发语法错误,因为
BETWEEN
与AND
一起使用,您将AND
放在单引号内。请将查询修改为:如果要在查询中使用
datetime
函数,请将其用作:yzuktlbb2#
试试看:
xxls0lw83#
筛选数据库中以字符串形式存储的日期。查询后,将其转换为“日期格式”,因此可以进行筛选。