none
SqlBulkCopy and datetime2 target database column RRS feed

  • Question

  • If you look at this Microsoft Documentation article:

    https://docs.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 datetimetype) 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 mean?

    If there is a better group to post this question - please let me know

    Monday, June 17, 2019 6:06 PM

All replies

  • Hi JoeSchmoe115,  

    Thank you for posting here.

    According to your problem, I have a question to confirm with you.

    Did you use SQL Server 2008 to do a test and find nothing fails? Or you want to confirm whether everything works well when using .Net 4.5 and SQL Server 2016 SP1. 

    We are waiting for your update.

    Besides, you can provide some code here, it is beneficial for us to make a test.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Tuesday, June 18, 2019 8:38 AM
    Moderator
  • Hi JoeSchmoe115,  

    Thank you for posting here.

    According to your problem, I have a question to confirm with you.

    Did you use SQL Server 2008 to do a test and find nothing fails? Or you want to confirm whether everything works well when using .Net 4.5 and SQL Server 2016 SP1. 

    We are waiting for your update.

    Besides, you can provide some code here, it is beneficial for us to make a test.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    I didn't use SQL 2008. I used .Net 4.5 and SQL Server 2016 SP1 for my application and nothing fails. Sorry, can't provide the code.
    Tuesday, June 18, 2019 11:51 AM
  • Hi JoeSchmoe115, 

    Thank you for your update.

    Based on your question, I have made a sample on my side.

    If we use System.DateTime, we will not get the error. However, If we use SqlDatetime, we will get the following exception.

    Exception:

    The documentation is for SqlDatetime instead of System.DateTime. Therefore,  it is not obsolete.

    Thank you for your question.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, June 19, 2019 8:07 AM
    Moderator