0

For my previous question, I implemented the solution from this SO link. I used the .NET libraries and used the two functions Out-DataTable and Write-DataTable in my solution.

Currently, I have my CSVs in place and using the Out-DataTable and Write-DataTable functions to insert data into SQL Server. However, it is not working with date data types. I get the error -

Write-DataTable : System.Management.Automation.MethodInvocationException: Exception calling "WriteToServer" with "1" argument(s): "The given value of type String from the data source cannot be converted to type date of the specified target column." --->

System.InvalidOperationException: The given value of type String from the data source cannot be converted to type date of the specified target column. ---> System.FormatException: Failed to convert parameter value from a String to a DateTime. ---> System.FormatException: String was not recognized as a valid DateTime. at System.DateTimeParse.Parse(String s, DateTimeFormatInfo dtfi, DateTimeStyles styles) at System.Convert.ToDateTime(String value, IFormatProvider provider) at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider) at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)

So when I looked into Out-DataTable function, it has another function named Get-Type defined in it, which basically does the column type conversion. Here is the function Get-Type - 

function Get-Type 
{ 
    param($type) 

$types = @( 
'System.Boolean', 
'System.Byte[]', 
'System.Byte', 
'System.Char', 
'System.Datetime', 
'System.Decimal', 
'System.Double', 
'System.Guid', 
'System.Int16', 
'System.Int32', 
'System.Int64', 
'System.Single', 
'System.UInt16', 
'System.UInt32', 
'System.UInt64') 

    if ( $types -contains $type ) { 
        Write-Output "$type" 
    } 
    else { 
        Write-Output 'System.String' 

    } 
} #Get-Type

Now SQL has a data type as date other than the .NET's datetime function. Hence, I am running into this error.

I had a thought of using parseexact method, but that again doesn't resolve the issue, since it resolves to type string. Also, Import-Csv takes in a flat file, hence the date in the format yyyy-MM-dd will always be of type string and my code would fail inevitably.

I have looked for a solution at this and this link, but I am not able to figure it out how to convert the string type into date. Any help will be much appreciated. Also, let me know if any extra info is required.

Vivek Kumar Singh
  • 3,223
  • 1
  • 14
  • 27
  • 1
    `parseexact` returns a datetime (at least a .net one)? So you could use that in a select statement as a caculated property to pass down the pipeline. I am unsure why that did not work for you. `[datetime]::ParseExact(((date).ToString("yyyy-MM-dd")),"yyyy-MM-dd",$null)` note the $null is in the culture placeholder so that might be significant depending on your local – Matt May 10 '18 at 15:10
  • The problem is that I don't have control over data until it is exported as a `CSV`. So nothing much working out from `SQL` side of things. I was hoping if anything could be done from `PoSh` end. `Import-Csv` takes in the data from a flat file and converts the unresolved type to `String`. Much appreciated @Matt! – Vivek Kumar Singh May 10 '18 at 16:59
  • 1
    But you have control to import the data yes before it is converted to a datatable? or is the csv import part of `write-tabledata` – Matt May 10 '18 at 17:15
  • @Matt - The data before the import comes from `Invoke-SqlCmd` command, the output of which is of type `array`. But I get what you are implying. There are other ways of invoking a `SQL` query the output of which will be of type `datatable`. I will try and let you know. Thanks for the pro-tip. – Vivek Kumar Singh May 10 '18 at 19:52
  • @Matt - It worked. Thanks a ton for the wonderful tip. I had so many things in my mind and so many things to look at that the thought of directly inserting from a PowerShell variable of type `array/datatable` didn't cross my mind. `Invoke-Sqlcmd's` result, when stored in a variable will preserve the table structure and it can directly be use with the aforementioned `Write-DataTable` function. – Vivek Kumar Singh May 16 '18 at 06:54

0 Answers0