title: How can I set column type when using SqlBulkCopy to insert into a sql_variant column
body: <p>I'm using SqlBulkCopy to insert/update from a .net DataTable object to a SQL Server table that includes a sql_variant column.  However SqlBulkCopy insists on storing DateTime values put into that column as sql type 'datetime' when what I need is 'datetime2'.  </p>
  
  <p>My DataTable is defined like this:</p>
  
  <pre><code>DataTable dataTable = new DataTable();
  dataTable.Columns.Add(new DataColumn("VariantValue", typeof(object))); //this represents my sql_variant column
  </code></pre>
  
  <p>Then I throw some data in there that requires a 'datetime2' to store.</p>
  
  <pre><code>DataRow row = dataTable.NewRow();
  row[0] = DateTime.MinValue;
  dataTable.Rows.Add(row);
  </code></pre>
  
  <p>And then I use SqlBulkCopy to get that data into Sql Server:</p>
  
  <pre><code>using (SqlBulkCopy bulk = new SqlBulkCopy(myConnection))
  {
       bulk.DestinationTableName = "tblDestination";     
       bulk.WriteToServer(dataTable);     
  }
  </code></pre>
  
  <p>My bulk copy will fail if a DateTime value is present in the data table that falls outside the range of the sql 'datetime' type (such as '1/1/0001').  That's why the column needs to be of type 'datetime2'.</p>
  
  <p>When you're writing normal insert statements that insert into a sql_variant column you can control what the type of the variant column is by using CAST or CONVERT.  For example:</p>
  
  <pre><code>insert into [tblDestination] (VariantValue) values (CAST('1/1/0001' AS datetime2))
  </code></pre>
  
  <p>Then if you were to display the actual type of the variant column like this:</p>
  
  <pre><code>SELECT SQL_VARIANT_PROPERTY(VariantValue,'BaseType') AS basetype FROM test
  </code></pre>
  
  <p>You'd see that indeed it is being stored as a 'datetime2'.</p>
  
  <p>But I'm using SqlBulkCopy and, as far as I know, there's no place to tell it that .net DateTime objects should be stored in columns of type 'datetime2' and not 'datetime'.  There's no place on the DataTable object, that I know of, to declare this either.  Can anyone help me figure out how to get this to happen?</p>