Insert object containing DateTime

[jasoncocks]

When Write-ObjectToSQL when inserting a DateTime, the table is created correctly but subsequent INSERTs rely on DateTime.ToString() ... this has local issues ...

(Get-Date).ToString() 21/06/2018 18:33:17

In order to insert date/time into SQL Server reliably as a nvarchar, Write-ObjectToSQL should convert date/time objects into the SQL Server default date/time format.

E.g.

I have modified the function including ... $dateformattypes = @{ # PS datatype = SQL data type 'System.DateTime' = 'datetime'; 'datetime' = 'datetime'; }

... if ( $numbertypes.ContainsKey( $datatype ) ){ $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")

                if ($($InputObject.$key) -ne $null){
                    if ($datatype -eq 'timespan' -or $datatype -eq 'System.TimeSpan') {
                        Write-Verbose "Timespan found ($key). Converting to ticks."
                        $null = $strBuilderValues.Append(", $(($InputObject.$key).Ticks)")
                    }else{
                        $null = $strBuilderValues.Append(", $($InputObject.$key)")
                    }
                    
                }else{
                    $null = $strBuilderValues.Append(", NULL")
                }
            **}elseif ( $dateformattypes.ContainsKey( $datatype ) ){
                $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")
                $strtmp = $InputObject.$key.ToString("yyyy-MM-dd HH:mm:ss.fff")
                if ($ConnectionString){ 
                    $null = $strBuilderValues.Append(", '$strtmp'")
                }else{
                    $null = $strBuilderValues.Append(", N'$strtmp'")
                }**
            }elseif ( $stringtypes.ContainsKey( $datatype ) ){
                $null = $strBuilderColumns.Append(", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast")
                $strtmp = $InputObject.$key -replace "'", "''"
                if ($ConnectionString){ 
                    $null = $strBuilderValues.Append(", '$strtmp'")
                }else{
                    $null = $strBuilderValues.Append(", N'$strtmp'")
                }

[jasoncocks]

(Get-Date).ToString() 21/06/2018 18:43:11

(Get-Date).ToString("yyyy-MM-dd HH:mm:ss.fff") 2018-06-21 18:43:28.619

[joelwiesmann]

I did more or less the same local modification by using .toString("o"). Would be really helpful to know that it will find it's way into the final code.

[JohnRoos]

This sounds like a great idea. Could you please create a pull request so it can be included in the module?