0

If you look at this Microsoft Documentation article:

https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlbulkcopy?redirectedfrom=MSDN&view=netframework-4.5.2

in the Remarks section it says:

SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008.

I am not observing this behavior (failure) and would like to know whether this documentation is possibly obsolete and SqlBulkCopy has been updated to support these data types.

More details on what I do:

  • I create DataTable as a source for SqlBulkCopy. The DataTable column is of type System.DateTime.
  • Target column in database table is of type datetime2.
  • I use .Net 4.5 and SQL Server 2016 SP1

Everything just works. I get no failures. I even tried passing "01/01/0001" date (outside of the range for SQL datetime type) in the DataTable and it gets saved as such in database.

I did find this 8 years old SO post: How can I set column type when using SqlBulkCopy to insert into a sql_variant column but I am not using sql_variant and nothing fails in my case.

Can anyone clarify what Microsoft documentation I quoted means?

Joe Schmoe
  • 1,574
  • 4
  • 23
  • 48

1 Answers1

2

SqlBulkCopy will fail when bulk loading a DataTable column of type SqlDateTime into a SQL Server column whose type is one of the date/time types added in SQL Server 2008.

But you wrote the type of your column is System.DateTime - so this remark is not relevant to your code.

In other words, if you created your data table like this:

var dt = new DataTable();
dt.Columns.Add("date column", typeof(SqlDateTime));
// ... other columns here

Then it would be relevant to your code.

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • 1
    Thanks, this was exactly it - I misunderstood what documentation meant. I changed my DataTable column type from System.DateTime to SqlDateTime to test and SqlBulkCopy failed with exception message saying about being unable to convert from datetime to datetime2. – Joe Schmoe Jun 18 '19 at 13:45
  • 1
    Glad to help :-) – Zohar Peled Jun 18 '19 at 13:53