Sue Hernandez's SharePoint Blog

SharePoint and Related Stuff

Monthly Archives: December 2009

Conversion failed when converting date and/or time from character string

When attempting to write a SQL Query to insert into a database, I was using something like the following statement below:

string sql = "INSERT INTO PurchaseRequest (ProjectGroupNumber, OrderType, DateRequested) ";
sql += " VALUES ('?', '?', '?')";

SqlCommand cmd = new SqlCommand(sql, conn);


cmd.Parameters.Add(new SqlParameter("@ProjectGroupNumber", _purchaseRequest.GeneralInformation.ProjectGroupNumber));
cmd.Parameters.Add(new SqlParameter("@OrderType", _purchaseRequest.GeneralInformation.OrderType));
cmd.Parameters.Add(new SqlParameter("@DateRequested", _purchaseRequest.GeneralInformation.DateRequested.Value.ToString()));

I would always get the following:

Could Not Insert into DB: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.

I found the solution to be use named parameters

string sql = "INSERT INTO PurchaseRequest (ProjectGroupNumber, OrderType, DateRequested) ";
sql += " VALUES (@ProjectGroupNumber, @OrderType, @DateRequested)";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@ProjectGroupNumber", _purchaseRequest.GeneralInformation.ProjectGroupNumber));
cmd.Parameters.Add(new SqlParameter("@OrderType", _purchaseRequest.GeneralInformation.OrderType));
cmd.Parameters.Add(new SqlParameter("@DateRequested", _purchaseRequest.GeneralInformation.DateRequested.Value));