How to convert .net DateTime to SQL Server DateTime2

I want to insert DateTime variable into database table column of DateTime2 type (from .ne开发者_运维百科t program) (im using sqlServer 2008, .net 4.0, c#)

now I have

     string insertCommand = @"INSERT INTO X VALUES (time.ToString());

Problem is in time string format. SQL Server does not work with output from ToString() function. I also tried .ToLongDate short date etc.. How to create proper sql command and how to format dateTime string to make this work?

Try this:

string sql = "insert into x values (@dt)";
SqlCommand cmd = cxn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@dt", time);
cmd.CommandText = sql;
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();

You need to add a parameter with your DateTime value.

not confirm but you can use use format times.tostring("dd/MMM/yyyy") like http://msdn.microsoft.com/en-us/library/az4se3k1.aspx