Powershell inserting DataTable with SqlBulkCopy, that hold datetime columns

datatable datetime powershell sqlbulkcopy sql-server

Question

I'm trying to insert a predefined DataTable into a SQL Server database, using Data.SqlClient.SqlBulkCopy.

There are different datatypes in the DataTable, but the problem is with Datetime columns.

Like this one:

$Datatable.Columns.Add((New-Object System.Data.DataColumn 'mydate',([datetime])))

I have data in the column, and to me it looks fine.

Like if I try $row.'mydate'.Month I get the month I would expect.

So it looks like the datatable is populated with the correct data.

But when I run

$bulkCopy.WriteToServer($DataTable)

I get this error:

The given value of type String from the data source cannot be converted to type datetime of the specified target column

So this is the first thing I don't get. Why does it say that my datatype is string, when it is datetime. Does SqlBulkCopy convert all data to strings ?

I've tried to reproduce the problem directly on the database, and this is what I got.

It has something to do with the formatting of the date. I can't insert this '22-02-2017'. But I can insert this '02-22-2017'.

So obviously there is some confusion in the datetime types.

Why does SqlBulkCopy try to insert '22' as month instead of day, when clearly data is correct in the datatable.

I'm thinking it might be a culture thing. But the database culture and my workstation culture seems to be aligned. However I did try to change the culture of my Powershell script, like this:

function Set-Culture([System.Globalization.CultureInfo] $culture)
{
    [System.Threading.Thread]::CurrentThread.CurrentUICulture = $culture
    [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture
}
$culture = [System.Globalization.CultureInfo]::GetCultureInfo('en-US')
Set-Culture $culture

This does switch the day and month, when I write out the data to the console, but it does not effect the BulkCopy error.

Do you guys have any idea what is going on ?

Popular Answer

You dataset sequence must be equal to table columns sequence.

CREATE TABLE [dbo].[tmp](
[IP] [varchar](16) NOT NULL,
[USER] [nvarchar](50) NOT NULL,
[DT] [date] NOT NULL



PS> $DataSet

    IP             User          DT               
    --             ----          --                   
172.16.11.11   SysCopyUser   2017-05-11 15:29:29 


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why