title: SqlBulkCopy Converting string to datetime with US Format
body: <p>I'm transferring date from an unknown source (Could be csv/SQL Server) to an application database, As i don't know where the data is coming from I first import the data into a holding table (all Varchar Columns) I then test is the value in the column matches what it should be and import to the database or flag a data error</p>
  
  <p>To transfer the data i load the data into a DataTable in C# then use the SqlBulkCopy to insert the data to the holding table.This is all working but i'm getting an issue with datetime columns. In the C# DataTable the date value is in YMD format and is stored as a string, the column in the holing table is a varchar(255) but at some point the transfer process the date is being converted to DMY format this is then causing an issue when I try to validate the dates in the holding table.</p>
  
  <p>To Load the DataTable I do:</p>
  
  <pre><code>cmd.CommandText = string.Format("SET DATEFORMAT YMD; {0}", cmd.CommandText);
  
  SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
  DataTable returnTable = new DataTable();
  
  dataAdapter.Fill(returnTable);
  </code></pre>
  
  <p>To Transfer the data I do:</p>
  
  <pre><code>    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(DBContext.ConnectionString, SqlBulkCopyOptions.KeepNulls))
      {
          bulkCopy.DestinationTableName = "HoldingTable";
          bulkCopy.BatchSize = 10000;
  
          foreach (impMap im in ImportMapping.Where(i =&gt; i.IsImported))
          {
              bulkCopy.ColumnMappings.Add(im.MappingColumn, im.TableField.FieldName);
          }
  
          foreach (SqlBulkCopyColumnMapping m in columnMappings)
          {
            bulkCopy.ColumnMappings.Add(m);
          }
  
          bulkCopy.WriteToServer(dataDataTable);  
     }
  </code></pre>