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 ?
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