How do I insert and query a DateTime object in SQLite DB from C# ?

Question

Hi All,

Consider this snippet of code:

string sDate = string.Format("{0:u}", this.Date);

           Conn.Open();
            Command.CommandText = "INSERT INTO TRADES VALUES(" + "\"" + this.Date + "\"" + "," +this.ATR + "," + "\"" + this.BIAS + "\"" + ")";
            Command.ExecuteNonQuery();

Note the "this.Date" part of the command. Now Date is an abject of type DateTime of C# environment, the DB doesnt store it(somewhere in SQLite forum, it was written that ADO.NET wrapper automatically converts DateTime type to ISO1806 format)

But instead of this.Date when I use sDate (shown in the first line) then it stores properly.

My probem actually doesnt end here. Even if I use "sDate", I have to retrieve it through a query. And that is creating the problem

Any query of this format

SELECT * FROM <Table_Name> WHERE DATES = "YYYY-MM-DD"

returns nothing, whereas replacing '=' with '>' or '<' returns right results.

So my point is:

How do I query for Date variables from SQLite Database.

And if there is a problem with the way I stored it (i.e non 1806 compliant), then how do I make it compliant

Answer 1

The ADO.NET wrapper can't convert the DateTime values to ISO 8601 (not 1806) if you convert it to a string and put it in the query. You need to use parameters for that:

Command.CommandText = "INSERT INTO TRADES VALUES (@Date, @Atr, @Bias)";
Command.Paramaters.Add("@Date", this.Date);
Command.Paramaters.Add("@Atr", this.ATR);
Command.Paramaters.Add("@Bias", this.BIAS);
Conn.Open();
Command.ExecuteNonQuery();

(Besides, you converted the DateTime value to a string and put in the sDate variable, but then you used the DateTime value to produce the SQL query anyway.)

The same applies when getting the data:

Command.CommandText = "SELECT * FROM <Table_Name> WHERE DATES = @Date";
Command.Paramaters.Add("@Date", theDate);

Answer 2

About your second problem, if SQLite is anything close to SQL Server,

SELECT * From where Dates = "YYYY-MM-DD' will not return because it will probably implicitily convert YYYY-MM-DD to YYYY-MM-DD 00:00:00. You might need to add a range (e.g. greater or equal than today and smaller than tomrrow).

Answer 3

@Guffa, I am getting "Cannot Convert from System.DateTime to System.Data.DbType" on this line:

Command.Parameters.Add("@Date", this.Date);