How to retrieve data from database using WHERE BETWEEN for variables in Sqlite c#

Question

I have an Sqlite row createDate in messages table. I want to retrieve all records from messages where createDate is between startDate and endDate local variables in method.

Any suggestion, how to write query for string stm? Or any other way how to do it

DateTime endDate = DateTime.Now;
DateTime startDate = endDate.AddDays(-7);

using (SQLiteConnection con = new SQLiteConnection(cs))
{
    con.Open();

    string stm = "SELECT * FROM messages WHERE createDate BETWEEN @startDate AND @endDate";

    using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
    {
        using (SQLiteDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                for (j = 0; j <= rdr.FieldCount - 1; j++)
                {
                    data = rdr.GetValue(j).ToString();
                    xlWorkSheet.Cells[i + 1, j + 1] = data;
                }
                i++;
            }
        }
    }
    con.Close();
}

You aren't binding `@startDate` and `@endDate` in the statement? — AKX, Aug 11 '21 at 06:08
@AKX yes, I want to compare createDate row from DB with local variables startDate and endDate, but don't know how to correctly write query — coxcoxgo, Aug 11 '21 at 06:20

score 0 · Answer 1 · answered Aug 11 '21 at 06:23

0

You should be able to bind the parameters like this (syntax borrowed from here):

string stm = "SELECT * FROM messages WHERE createDate BETWEEN @startDate AND @endDate";

using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
   cmd.Parameters.AddWithValue("@startDate", startDate));
   cmd.Parameters.AddWithValue("@endDate", endDate));
   // ...

answered Aug 11 '21 at 06:23

AKX

152,115
15
115
172

but there is no startDate and endDate in DB, how it could be useful? – coxcoxgo Aug 11 '21 at 06:58
The `@startDate` and `@endDate` parameter placeholders in your statement are replaced with the values of those local variables you have. – AKX Aug 11 '21 at 07:22

score 0 · Answer 2 · answered Aug 11 '21 at 06:31

SQLLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

SELECT * FROM test WHERE date BETWEEN "2021-02-11" AND "2021-08-11";

How to retrieve data from database using WHERE BETWEEN for variables in Sqlite c#

2 Answers2