How to insert and retrieve TIMESTAMP fields using SQL statement with SQLite?

Question

I was able to insert TIMESTAMP as a string into my table, but when I read it out my program raises an error saying "String was not recognized as a valid DateTime."

Here is how I insert my values into the table.

sqlstr := 'INSERT INTO ALARMS (ALARMTIME) VALUES ("'+DateTime.Now.ToString+'");';
cnn := new SQLiteConnection('Data Source=AlarmLog.s3db');
cnn.Open;
var mycommand := new SQLiteCommand(cnn);
mycommand.CommandText := sqlstr;
var info := mycommand.ExecuteNonQuery;
cnn.Close;

Here is how I read it out.

    sqlstr := 'SELECT * from ALARMS';
    var dt := new DataTable;
    cnn := new SQLiteConnection('Data Source=AlarmLog.s3db');
    cnn.Open;
    var mycommand := new SQLiteCommand(cnn);
    mycommand.CommandText := sqlstr;
    var reader := mycommand.ExecuteReader;
    dt.Load(reader); <---------------------Here is where I am getting the error, "String was not recognized as a valid DateTime."
    reader.Close;
    cnn.Close;

I know why I am getting the error, but why can't I insert the TIMESTAMP as a date and time instead of string or text type. If you notice in my SQL query, I have quotation marks around the DateTime.Now.ToString statement. That's the only way it accepts my SQL Statement.

[SQLite does not have a storage class set aside for storing dates and/or times.](http://www.sqlite.org/datatype3.html) — Andomar, Jul 26 '12 at 19:51
@Andomar, So basically that means you have to convert datetime into string and pass that to your table. For retrieving you need to read it as string and converted into actual datetime value. — ThN, Jul 26 '12 at 20:14
Yes, and of course it helps if you choose a format that both SQLite and .NET understand. `yyyy-MM-ddTHH:mm:ss` is kind of a universal format. — Andomar, Jul 27 '12 at 05:45

score 1 · Accepted Answer · answered Jul 26 '12 at 19:08

Pass a date format that SQLite recognizes, like:

DateTime.Now.ToString("yyyy-MM-ddTHH:mm")

Other valid formats on this SQLite manual page.

Even better is to use a parameter, like:

sqlstr := 'INSERT INTO ALARMS (ALARMTIME) VALUES ($dt);';
...
mycommand.Parameters.AddWithValue("$dt", DateTime.Now);

How to insert and retrieve TIMESTAMP fields using SQL statement with SQLite?

1 Answers1