CODEWITHSUNDEEP
sqlitekotlin
Joker
Joker

Reputation: 856

Retrive date wise data from SQLITE database kotlin

I have one SQlite database where i store some data with date.

Now i want to get data date wise like:

  1. Month wise - it means i pass the value like Current date is EndDate to this month 1st date.

  2. Year wise - it means i want to get data 1st-april-20..previews to 31-march-20..current

  3. Start and End date wise - hear is which i pass date.

For that i got one solution is HERE for java.

But i have no idea this HOW TO WORK. Anyone please explain me how to work this and How to i get data as i mention above. FOR KOTLIN

TABLE

db.createTable(customerDetail, true,
                credit_id to INTEGER + PRIMARY_KEY + AUTOINCREMENT,
                credit_type to TEXT,
                c_id to TEXT,
                credit_amount to TEXT,
                credit_date to TEXT,
                addFrom to TEXT
        )

UPDATE

For Month wise data i'll try below query like:

"SELECT * FROM $customerDetail WHERE $credit_date BETWEEN strftime('%d-%m-%Y', '$startDate') AND strftime('%d-%m-%Y', '$currentDate')"
/*SELECT * FROM CustomerDetail WHERE credit_date BETWEEN strftime('%d-%m-%Y', '01/02/2019') AND strftime('%d-%m-%Y', '23/02/2019')*/

But it's give me arralistSize = 0.

After that i can write new query like:

"SELECT * FROM $customerDetail WHERE $credit_date BETWEEN '$startDate' AND '$currentDate'"
/*SELECT * FROM CustomerDetail WHERE credit_date BETWEEN '01/02/2019' AND '23/02/2019'*/

In this query data will return. But it's return all data without any filtering.

If anyone knows why this work like this please help me.

MY DATA LIST

enter image description here

Thanks in advance.

Solution :

Solution for MONTH wise

I just change date format "dd/mm/yyyy" TO "yyyy/mm/dd" and re insert all data.

AND Fire below QUERY :

"SELECT * FROM $customerDetail WHERE $credit_date BETWEEN '$startDate' AND '$currentDate'"

Upvotes: 1

Views: 802

Answers (1)

forpas
forpas

Reputation: 164174

SQLite does not have a Date data type like other RDBMS's. It treats dates as TEXT.
So when it compares the credit_date column it actually does compare strings.
This would be fine if you stored your dates in the format YYYY-MM-DD and compared against the same format.
But if you store your dates in the format DD/MM/YYYY then you can't compare.
So the best solution would be to change the format of the column credit_date to YYYY-MM-DD.
If this is not possible then you have to transform the string value of the column like this:

substr(credit_date, 7, 4) || '-' || substr(credit_date, 4, 2) || '-' || substr(credit_date, 1, 2)

Now you can use this in a query like:

SELECT * FROM $customerDetail 
WHERE substr($credit_date, 7, 4) || '-' || substr($credit_date, 4, 2) || '-' || substr($credit_date, 1, 2)
BETWEEN '$startDate' AND '$currentDate'

But you have to make sure that $startDate and $currentDate are also in the format YYYY-MM-DD

Upvotes: 0

Related Questions