1

I am using C# (.NET) and SQLite database with it. I have a table in an SQLite database with a column called "InvoiceDate". I have chosen the datatype (in the db table) for the same as TEXT as I need it to be a datetime variable.

I am using the System.Data.SQLite reference.

The following is my command text where I am facing the problem:

command.CommandText = "SELECT * FROM InvoiceMaster WHERE InvoiceDate BETWEEN '" 
                     + date1.ToString() + "' AND '" 
                     + date2.ToString() + "' ORDER BY InvoiceNumber";

I need to find all results where the column InvoiceDate falls between the given dates date1 and date2. But the problem is that I am getting the results even though I choose other dates for example I get the same results for the same month and dates even though I choose a different year. There is something wrong with the command text and I also need to know what type of datatype should I choose in the db table. Please do let me know how I should be writing the select command.

Improve this question
5
  • 3
    Possible SQL Injection Use prepared statement/parameter binding and your problem will be solved. Commented Mar 6, 2016 at 15:18
  • Can you please give me a sample code... Commented Mar 6, 2016 at 15:23
  • 2
    Did you do basic reasearch first? You have example in provided link Commented Mar 6, 2016 at 15:24
  • just trying to find it in google..... Commented Mar 6, 2016 at 15:24
  • 1
    You need to format the date strings correctly... Commented Mar 6, 2016 at 15:25

3 Answers 3

Reset to default
1

You can create a method to convert your datetime

 private string DateTimeSQLite(DateTime datetime)
  {
    string dateTimeFormat = "{0}-{1}-{2} {3}:{4}:{5}.{6}";
    return string.Format(dateTimeFormat, datetime.Year,   
                         datetime.Month,datetime.Day, 
                         datetime.Hour, datetime.Minute,  
                          datetime.Second,datetime.Millisecond);
  }

or better make it a extension method.

 private static string DateTimeSQLite(this DateTime datetime)
 {}

Also use parametrized queries to avoid sql injection

string commandText = "SELECT * FROM InvoiceMaster 
                  WHERE InvoiceDate BETWEEN @date1 and @date2
                  ORDER BY InvoiceNumber"
yourcommand.Parameters.Add("@date1",date1.DateTimeSQLite());
yourcommand.Parameters.Add("@date2",date1.DateTimeSQLite());
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I don't think that's a good idea because single digit month, day, hour, minute and seconds part will not have leading zero.
1

From 1.2 Date and Time Datatype

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")

Since ToString() does not generate this kind of format, you can use custom formatting like;

date1.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture)
date2.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture)

But more important, you should always use parameterized queries. This kind of string concatenations are open for SQL Injection attacks.

I'm not %100 about format but you might wanna use The "o" standard format specifier which represents;

.. a custom date and time format string using a pattern that preserves time zone information and emits a result string that complies with ISO 8601.

command.CommandText = @"SELECT * FROM InvoiceMaster 
                        WHERE InvoiceDate BETWEEN @date1 AND @date2 
                        ORDER BY InvoiceNumber";

command.Parameters.AddWithValue("@date1", date1.ToString("o"));
command.Parameters.AddWithValue("@date2", date1.ToString("o));
Improve this answer

5 Comments

That interesting because ISO 8601 has format yyyy-mm-ddThh:mm:ss[.mmm]. SQLite doc indicates that T is not part of this standard.
ISO 8601 is culture independent. But not in format presented in SQLite doc. Please check demo and correct one demo2
@lad2025 You have a point but I wrote this format because the doc say so. I never used Sqlite honestly that's why I'm not %100 sure.
Tried the following: command.CommandText = "SELECT * FROM InvoiceMaster WHERE InvoiceDate BETWEEN 'DATETIME(" + date1.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + ")' AND 'DATETIME(" + date2.ToString("yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture) + ")'"; STILL DOESN'T WORK
@SlickGuy I updated my answer. Can you please take a look?
0

Since you have chosen to store the dates as TEXT field in the database, they must be formatted using ISO8601. Example:

2016-02-03T20:34:22Z

So once you have ensured that they are stored this way all that's left is parametrize your query:

DateTime date1 = ... get from somewhere
DateTime date2 = ... get from somewhere

using (var conn = new SQLiteConnection("Data Source=mydb.db;Version=3;"))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT * FROM InvoiceMaster WHERE InvoiceDate BETWEEN @startDate AND @endDate";
    cmd.Parameters.AddWithValue("@startDate", date1.ToString("o"));
    cmd.Parameters.AddWithValue("@endDate", date2.ToString("o"));
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // do something with the results here
        }
    }
}

Notice how I am using the .ToString("o") format specifier to ensure that the dates will be passed correctly to the database.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.