How can you send a correct datetime object to SQL Server in powershell?

Question

I have scoured the internet, and so far, none of the answers provide anything I've been able to use.

I am having serious troubles getting a simple datetime into the SQL Server database with powershell.

The column is of type "datetime" and the language is en-US.

An example of what is in the column value would be

2009-09-16 00:00:00.000

I have attempted to send this same date to the database in powershell, and everything I try has returned a

"Conversion failed when converting date and/or time from character string."

error.

This is my powershell script:

$dateString = "2009-09-16 00:00:00.000"
$id = '12345'
$nd  = Get-Date $dateString -Format "yyyy-MM-dd HH:mm:ss:fff"
$query = "Update MyTable Set CreateDate = '@e1' Where ID = '@e2'"
$SqlCmd.Parameters.Clear()
$parm = $SqlCmd.Parameters.Add("@e1", $nd)
$SqlCmd.Parameters.Add("@e2" , $id)
$SqlCmd.CommandText = $query
$SqlCmd.ExecuteNonQuery()

I have tried using [datetime] for the variable, but it always converts whatever string it's looking at into something unlike the format needed above, so it fails.

Everything else, regardless of the fact that it looks right, fails due to the wrong type.

Is there a syntax that will get what is needed to insert/update?

The easest way is to use the unseparated language neutral format YYYYMMDD hh:mm:ss[.mmm] or the YYYY-MM-DDThh:mm:ss[.nnnnnnn] format defined in ISO 8601. Both formats are not affected by the SET LANGUAGE and SET DATEFORMAT session locale settings. More in Tibor Karaszi's article The ultimate guide to the datetime datatypes. — Andrei Odegov
– Andrei Odegov, Commented Dec 23, 2018 at 14:29

mklement0 · Accepted Answer · 2018-12-23 19:06:07Z

$param = $SqlCmd.Parameters.Add("@e1", $nd)

Add(String, Object) is obsolete. Do not use this method because the system guesses which data type to use and will often assume it's a string. Use Add(String, SqlDbType) or Add(String, SqlDbType, Int32) instead (the latter is when you need to specify the length such as with a varchar(10)).

Here, I would use this:

$SqlCmd.Parameters.Add("@e1", [System.Data.SqlDbType]::DateTime).Value = $nd
$SqlCmd.Parameters.Add("@e2", [System.Data.SqlDbType]::Int).Value = $id

Note that you should use the actual data type for the second value here. I'm just guessing that it's an int. The System.Data.SqlDbType enum has data types for all the common SQL Server data types.

Additionally, your query is incorrect. You do not want to quote parameters. Instead of this:

$query = "Update MyTable Set CreateDate = '@e1' Where ID = '@e2'"

Specify this:

$query = "Update MyTable Set CreateDate = @e1 Where ID = @e2"

I'd also recommend using more descriptive parameter names. There's no reason not to use @CreateDate and @id.

Finally, this doesn't return a datetime at all, it just returns another string:

$nd  = Get-Date $dateString -Format "yyyy-MM-dd HH:mm:ss:fff"

The -Format parameter specifies the output format for a string.

This is the code I would use:

$dateString = "2009-09-16 00:00:00.000"
$id = '12345'
$nd  = Get-Date $dateString

$ConnectionString = ...
$query = "Update MyTable Set CreateDate = @CreateDate Where ID = @id"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection -ArgumentList $ConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand -ArgumentList $query, $SqlConnection
$SqlCmd.Parameters.Add("@CreateDate", [System.Data.SqlDbType]::DateTime).Value = $nd
$SqlCmd.Parameters.Add("@id", [System.Data.SqlDbType]::Int).Value = $id

try {
    $SqlConnection.Open()
    $RecordsAffected = $SqlCmd.ExecuteNonQuery()
    Write-Host "$RecordsAffected row(s) updated."
}
finally {
    $SqlConnection.Close()
}

Esperento57 · Accepted Answer · 2018-12-23 03:00:53Z

Hum, Always Dangerous to search on a database with a query builded with string concatenated. This approach however is NOT recommended because it is prone to SQL injection attacks.

But you can try this:

$query = "Update MyTable Set CreateDate = @e1 Where ID = '@e2'"
$SqlCmd.Parameters.Clear()
$SqlCmd.Parameters.Add("@e1", [System.Data.SqlDbType]::DateTime)
$param1.Value=(Get-Date -Year 2009 -Month 9 -Day 16 "yyyy-MM-dd HH:mm:ss:fff").Date
$SqlCmd.Parameters.Add("@e2" , $id)
$SqlCmd.CommandText = $query
$SqlCmd.ExecuteNonQuery()

or may be this:

$id = '12345'
$dateString = "2009-09-16 00:00:00.000"
$query = "Update MyTable Set CreateDate = convert(datetime, '@e1', 121)  Where ID = '@e2'"
$SqlCmd.Parameters.Clear()
$SqlCmd.Parameters.Add("@e1", $dateString)
$SqlCmd.Parameters.Add("@e2" , $id)
$SqlCmd.CommandText = $query
$SqlCmd.ExecuteNonQuery()

Collectives™ on Stack Overflow

How can you send a correct datetime object to SQL Server in powershell?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related