My SQLite database stores invoices with invoice number and date and time created. The date column is of type TEXT as YYYY-mm-dd hh: MM: ss (2018-02-12 03:02:59).
If user selects 2018-02-01 I want to show invoice numbers generated between 2018-02-01 00:00:00 and current date (which is 2017-02-12 23:59:59). Some said to change to UNIX epoch time format but that's not possible for now. How should I create my select query to get desired results?
I tried:
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:
> 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)
Use this query:
SELECT * FROM YourTableName WHERE DateTimeColumnName BETWEEN UserInputDate AND DATETIME('NOW', 'LOCALTIME')
This will result all rows with date-time between current time and date given as input by user. LOCALTIME is to compensate for you timezone as SQLite datetime function uses UTC timezone.
Edit after error in query is added into question:
Your query you have used throws syntax error because BETWEEN is used with AND, you have taken AND inside single-quotes. Modify your query as:
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)
If you want to use datetime function in your query, use it as:
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 DATETIME('NOW', 'LOCALTIME') ORDER BY bill_date ASC)