title: How to resolve implicit conversion differences between SQL Server and .NET when converting from string to another data type
body: <h2>Background</h2>
  
  <p>We have a VB.NET application that loads text files into database tables. Text files can be in a variety of formats (Flat file (fixed column width), character delimited) and can have complex structures (e.g. variable number of columns). The format and structure of the file being imported is known in advance.</p>
  
  <p>Before commencing file import, the application loads the "file format/structure" configuration settings. The application, then, reads the file and builds an SQL string (<code>INSERT( [col list] ) VALUES( 'value1', 'value2', .. ), ..</code>), which is then sent to the target table.<br>
  <strong>Update:</strong> Because all data comes as text (from a text file) the application also inserts it as text into a database, letting database do all the implicit conversion work. Having said above, there is some formatting being done (e.g. American style 06-05-2016 would be converted to 2016-06-05) based on configuration settings for a give type of file.</p>
  
  <p>Target tables columns can have non-VARCHAR data types e.g. <code>INT</code>, <code>DATETIME</code>, <code>DECIMAL( x, y )</code> etc. So inserting string data causes implicit VARCHAR->other data type conversions.<br>
  It is of note that SQL Server is able to convert empty strings to default values for certain types (the most notable examples are '' -> '1900-01-01' are '' -> '0')</p>
  
  <p>This application is (was) a bit of a spaghetti monster, which I had to untangle before I could make my changes. As part of this "untanglement process" I was looking to replace SQL string building with SqlBulkCopy (using DataTable as cache object).</p>
  
  <h2>Question</h2>
  
  <p>SqlBulkCopy seems to first convert strings into CLR types before sending the data over to SQL Server. CLR types have different rules for implicit string to non-string type conversions so my data cannot get imported as-is (even though it imports just fine using the above SQL String building approach).</p>
  
  <p>Is there a way to work around this?</p>
  
  <p>What I have found so far:
  This question mentions custom “clean up” functions may need to be implemented. <a href="https://stackoverflow.com/questions/18140012/sqlbulkcopy-the-given-value-of-type-string-from-the-data-source-cannot-be-conv">SqlBulkCopy - The given value of type String from the data source cannot be converted to type money of the specified target column</a><br>
  IMO custom "clean up" / coversion functions solution to replicate SQL Server behaviour is "akward".  </p>
  
  <p>I have also tried to play around with DataTable column types but to no avail:</p>
  
  <p>SQL Table is declared as follows:</p>
  
  <pre><code>CREATE TABLE LoadTable( IntVal INT NULL, BitVal BIT NULL, DateVal DATETIME NULL, DecVal DECIMAL( 12, 3 ) NULL )
  </code></pre>
  
  <p>.NET Code:
  </p>
  
  <pre class="lang-vb prettyprint-override"><code>'DTColumns(1) = New System.Data.DataColumn("BitVal", GetType(SqlTypes.SqlString))
  'DTColumns(1) = New System.Data.DataColumn("BitVal", GetType(System.String))
  DTColumns(1) = New System.Data.DataColumn("BitVal")
  CacheRow.Item("BitVal") = "1"
  BulkCopier.WriteToServer(_DataCache)
  </code></pre>
  
  <p>But always get Exceptions telling me that cannot convert value to Boolean:</p>
  
  <blockquote>
    <p>The given value of type String from the data source cannot be
    converted to type bit of the specified target column.<br>
    Failed to convert parameter value from a String to a Boolean.  </p>
  </blockquote>
  
  <p>Another one:  </p>
  
  <blockquote>
    <p>Failed to convert parameter value from a SqlString to a Boolean.  </p>
  </blockquote>
  
  <p>Implicit default values do not work either (do <strong>NOT</strong> confuse with <code>DEFAULT CONSTRAINT</code>):  </p>
  
  <pre class="lang-vb prettyprint-override"><code>DTColumns(2) = New System.Data.DataColumn("DateVal", GetType(SqlTypes.SqlString))
  CacheRow.Item("DateVal") = ""
  </code></pre>
  
  <p>On the other had, this works in SQL:</p>
  
  <pre class="lang-vb prettyprint-override"><code>INSERT LoadTable( IntVal, BitVal, DateVal )
  SELECT '', '', ''
  /* Result
  IntVal  BitVal  DateVal
  0       0       1900-01-01 00:00:00.000
  */
  </code></pre>
  
  <p>It clearly proves that SqlBulkCopy internally converts an incoming data type to a CLR equivalent destination type.</p>
  
  <p>Bonus question: anyone knows the reason why SqlBulkCopy must convert everything to a CLR type? Seems strange given it is writing data to a non CLR target.</p>
  
  <p><strong>Clarification:</strong>
  I fully understand that data types have to be converted (implicitly or explicitly). I am looking for a solution to convert strings to other types in exacly the same way as SQL Server does. My problem is that .NET does it differently, so simply using Integer.Parse (or equivalents) will fail in some cases, where in SQL the same conversion succeeds. I have provided a few examples where such conversions fail but there are plenty more that I know of (and still more that I don't).</p>