Error in Querying sqlite-net db based on a date range

Question

I have a problem for which I am writing one search query to do a search based on Date range which is in a string format. The problem is I am not able to query between two date ranges I specify.

This is my query::

String query = "SELECT DISTINCT * FROM Object WHERE (( TransactionAccount = '" + parameter + "') AND (CAST(Amount AS REAL) BETWEEN " + min_amount + " AND " + max_amount + ") AND Date BETWEEN   Date(2012-01-02)  AND  Date(2014-01-02)  ) ";

querylist = dbConn.Query<Passbook_transaction.Object>(query).ToList<Passbook_transaction.Object>(); ;

But its returning SQLite Exception.

The error seems to be in the "Date" because removing the date querying works.

Date is the name of the function and the name of your column? I can see a conflict here. By the way, are you sure that Date (the function) requires just one parameter without the string quoting? — Steve
– Steve, Commented Feb 14, 2014 at 15:02
Hi, Now its not returning errors but the querylist size is shown as empty. even though there are transactions between those date range. — ASP
– ASP, Commented Feb 15, 2014 at 5:02

mnieto · Accepted Answer · 2014-02-14 14:57:29Z

1

try this, quoting dates:

String query = "SELECT DISTINCT * FROM Object WHERE (( TransactionAccount = '" + parameter + "') AND (CAST(Amount AS REAL) BETWEEN " + min_amount + " AND " + max_amount + ") AND Date BETWEEN   Date('2012-01-02')  AND  Date('2014-01-02')  ) ";

answered Feb 14, 2014 at 14:57

mnieto

3,9044 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ASP · Accepted Answer · 2014-02-18 11:28:32Z

1

Sorted out the issue by adding the date in db in format "yyyy-MM-dd HH:mm:ss"(which is accepted by SQLite) and using the query below:

 String query = "SELECT DISTINCT * FROM Object WHERE (( TransactionAccount = '" + parameter + "') AND (CAST(Amount AS REAL) BETWEEN " + min_amount + " AND " + max_amount + ") AND TDate BETWEEN   Date('2014-01-01 19:45:46')  AND  Date('2014-02-01 19:05:43')  ) ";

Thanks all for comments and answers.

edited Feb 18, 2014 at 11:28

answered Feb 15, 2014 at 5:22

ASP

3,7051 gold badge33 silver badges46 bronze badges

Collectives™ on Stack Overflow

Error in Querying sqlite-net db based on a date range

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related