CODEWITHSUNDEEP
c#winformssqlite
Kratos
Kratos

Reputation: 205

SQLite command to insert current datetime not working through c#

I am trying to insert current date in my table from a c# application, i am doing this the following way:

sqlite_cmd.CommandText = "INSERT INTO tablename (column1,creation_date) VALUES ('abcd_" + (id) + "',CURRENT_TIMESTAMP);";
                sqlite_cmd.ExecuteNonQuery();

But this throws error saying that CURRENT_TIMESTAMP is not a valid field. I double checked the above query using sqlite manager plugin for firefox and it seems to work correctly.

P.S. - id in the above query is an int variable that is set by c# application.

Upvotes: 2

Views: 7039

Answers (2)

user240141
user240141

Reputation:

Try using this:

using(SQLiteCommand cmd = new SQLiteCommand(yourSqliteConnection))
{
cmd.CommandText = "INSERT INTO tablename (column1,creation_date) VALUES (?,?)";
cmd.Command.Parameters.AddWithValue("@p1",string.Format("abcd_{0}",id));
cmd.Command.Parameters.AddWithValue("@p2",DateTime.Now.ToString("s"));
cmd.CommandType= CommmandType.Text;

cmd.ExecuteNonQuery();
}

DateTime.Now.ToString("s") means convert this datetime into XSD format. SQLite does not have a DateTime Format in its own. so everything you need is to convert it into a fixed format of TEXT and then insert and serach according to this format.

XSD is a fixed format for DateTime. it does not change if format of DateTime Change.

Remember when you need to do a search (SELECT) command based on DateTime try to convert it as shown above before doing any operation in SQLite.

Upvotes: 2

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

CURRENT_TIMESTAMP is only valid in column definition.

As a expression, you can use DATETIME('NOW'). Try:

 sqlite_cmd.CommandText = "INSERT INTO tablename (column1,creation_date) VALUES ('abcd_" + (id) + "', DATETIME('NOW'));";

Upvotes: 4

Related Questions