title: Conversion failed when converting datetime from character string
body: <p>I am developing a C# VS 2008 / SQL Server 2005 Express website application.  I have tried some of the fixes for this problem but my call stack differs from others.  And these fixes did not fix my problem.  What steps can I take to troubleshoot this?</p>
  
  <p>Here is my error:</p>
  
  <pre><code>System.Data.SqlClient.SqlException was caught
    Message="Conversion failed when converting datetime from character string."
    Source=".Net SqlClient Data Provider"
    ErrorCode=-2146232060
  
    LineNumber=10
    Number=241
    Procedure="AppendDataCT"
    Server="\\\\.\\pipe\\772EF469-84F1-43\\tsql\\query"
    State=1
    StackTrace:
         at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
         at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
         at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
         at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
         at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
         at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
         at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
         at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
         at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
         at ADONET_namespace.ADONET_methods.AppendDataCT(DataTable dt, Dictionary`2 dic) in c:\Documents and Settings\Admin\My Documents\Visual Studio 2008\WebSites\Jerry\App_Code\ADONET methods.cs:line 102
  </code></pre>
  
  <p>And here is the related code.  When I debugged this code, "dic" only looped through the 3 column names, but did not look into row values which are stored in "dt", the Data Table.</p>
  
  <pre><code>public static string AppendDataCT(DataTable dt, Dictionary&lt;string, string&gt; dic)
  {
      if (dic.Count != 3)
          throw new ArgumentOutOfRangeException("dic can only have 3 parameters");
  
      string connString = ConfigurationManager.ConnectionStrings["AW3_string"].ConnectionString;
      string errorMsg;
  
      try
      {               
          using (SqlConnection conn2 = new SqlConnection(connString))
          {
              using (SqlCommand cmd = conn2.CreateCommand())
              {
                  cmd.CommandText = "dbo.AppendDataCT";
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.Connection = conn2;
                  foreach (string s in dic.Keys)
                  {
                      SqlParameter p = cmd.Parameters.AddWithValue(s, dic[s]);
                      p.SqlDbType = SqlDbType.VarChar;
                  }
  
                  conn2.Open();
                  cmd.ExecuteNonQuery();
                  conn2.Close();
                  errorMsg = "The Person.ContactType table was successfully updated!";
              }
          }
      }
  </code></pre>
  
  <p>Here is my SQL stored proc:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[AppendDataCT] 
  @col1 VARCHAR(50), 
  @col2 VARCHAR(50),
  @col3 VARCHAR(50)
  AS
  BEGIN
  SET NOCOUNT ON;
  DECLARE @TEMP DATETIME
  SET @TEMP = (SELECT CONVERT (DATETIME, @col3))
  
  INSERT INTO Person.ContactType (Name, ModifiedDate)
  VALUES( @col2, @TEMP)
  END
  </code></pre>
  
  <p>The input file has 3 columns.  The first two are varchars, but the 3rd one is also varchar I think, but it's represented as "3/11/2010".  In this input file, a sample row looks like:
  "Benjamin|breakfast|3/11/2010".
  And I am trying to convert this date field from a string to a datetime here in my SP.  Am I going about it the wrong way? </p>
  
  <p>DataRow:
  col1|col2|col3
  11|A2|1/10/1978
  12|b2|2/10/1978
  13|c2|3/10/1978
  14|d2|4/10/1978</p>