SQLite – Query to filter data by dates stored in form of TEXT

Issue

I have an SQLite Database which stores the data of Invoices generated in a store by the customers. I have a table which stores Invoice numbers and the date and time in which they were created. The date column is of data type TEXT and has a format of YYYY-mm-dd hh: MM: ss (2018-02-12 03:02:59) like this. I want to filter the results with respect to date provided by the user. If user select 2018-02-01 as query date I want to show all invoice numbers generated between 2018-02-01 00:00:00 to current date which is 2017-02-12 23:59:59.I searched a lot on the web but I am unable to find the relevant answers. Some answers said to change the whole database into UNIX epoch time format but that’s not possible for now in my case.How should I create my select query in order to get the desired results?

I used the following SQLite command

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)

But My app crashed and Showd an 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)

Solution

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)

Answered By – global_warming

Answer Checked By – Terry (FlutterFixes Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *