{"action":"opened","issue":{"url":"https://api.github.com/repos/JohnRoos/PowerShell/issues/9","repository_url":"https://api.github.com/repos/JohnRoos/PowerShell","labels_url":"https://api.github.com/repos/JohnRoos/PowerShell/issues/9/labels{/name}","comments_url":"https://api.github.com/repos/JohnRoos/PowerShell/issues/9/comments","events_url":"https://api.github.com/repos/JohnRoos/PowerShell/issues/9/events","html_url":"https://github.com/JohnRoos/PowerShell/issues/9","id":334593174,"node_id":"MDU6SXNzdWUzMzQ1OTMxNzQ=","number":9,"title":"Insert object containing DateTime","user":{"login":"jasoncocks","id":40471681,"node_id":"MDQ6VXNlcjQwNDcxNjgx","avatar_url":"https://avatars1.githubusercontent.com/u/40471681?v=4","gravatar_id":"","url":"https://api.github.com/users/jasoncocks","html_url":"https://github.com/jasoncocks","followers_url":"https://api.github.com/users/jasoncocks/followers","following_url":"https://api.github.com/users/jasoncocks/following{/other_user}","gists_url":"https://api.github.com/users/jasoncocks/gists{/gist_id}","starred_url":"https://api.github.com/users/jasoncocks/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/jasoncocks/subscriptions","organizations_url":"https://api.github.com/users/jasoncocks/orgs","repos_url":"https://api.github.com/users/jasoncocks/repos","events_url":"https://api.github.com/users/jasoncocks/events{/privacy}","received_events_url":"https://api.github.com/users/jasoncocks/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-06-21T17:44:38Z","updated_at":"2018-06-21T17:44:38Z","closed_at":null,"author_association":"NONE","body":"When Write-ObjectToSQL when inserting a DateTime, the table is created correctly but subsequent INSERTs rely on DateTime.ToString() ...  this has local issues ...\r\n\r\n(Get-Date).ToString()\r\n21/06/2018 18:33:17\r\n\r\nIn 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.\r\n\r\nE.g.\r\n\r\nI have modified the function including ...\r\n        $dateformattypes = @{\r\n        #   PS datatype       = SQL data type\r\n            'System.DateTime' = 'datetime';\r\n            'datetime'        = 'datetime';\r\n        }\r\n\r\n...\r\n                if ( $numbertypes.ContainsKey( $datatype ) ){\r\n                    $null = $strBuilderColumns.Append(\", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast\")\r\n\r\n                    if ($($InputObject.$key) -ne $null){\r\n                        if ($datatype -eq 'timespan' -or $datatype -eq 'System.TimeSpan') {\r\n                            Write-Verbose \"Timespan found ($key). Converting to ticks.\"\r\n                            $null = $strBuilderValues.Append(\", $(($InputObject.$key).Ticks)\")\r\n                        }else{\r\n                            $null = $strBuilderValues.Append(\", $($InputObject.$key)\")\r\n                        }\r\n                        \r\n                    }else{\r\n                        $null = $strBuilderValues.Append(\", NULL\")\r\n                    }\r\n                **}elseif ( $dateformattypes.ContainsKey( $datatype ) ){\r\n                    $null = $strBuilderColumns.Append(\", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast\")\r\n                    $strtmp = $InputObject.$key.ToString(\"yyyy-MM-dd HH:mm:ss.fff\")\r\n                    if ($ConnectionString){ \r\n                        $null = $strBuilderValues.Append(\", '$strtmp'\")\r\n                    }else{\r\n                        $null = $strBuilderValues.Append(\", N'$strtmp'\")\r\n                    }**\r\n                }elseif ( $stringtypes.ContainsKey( $datatype ) ){\r\n                    $null = $strBuilderColumns.Append(\", $quoteFirst$prekey$($key.Replace(' ','_'))$quoteLast\")\r\n                    $strtmp = $InputObject.$key -replace \"'\", \"''\"\r\n                    if ($ConnectionString){ \r\n                        $null = $strBuilderValues.Append(\", '$strtmp'\")\r\n                    }else{\r\n                        $null = $strBuilderValues.Append(\", N'$strtmp'\")\r\n                    }\r\n\r\n"}}
{"actor":{"display_login":"jasoncocks"}}