Convert string from PowerShell into sqldbtype. datetime

Question

I have two parameters, from and to date. I need to pass from PowerShell to an SQL stored procedure. I created a GUI to input the parameters in the format of 'YYYY-MM-DD'. However, it's a string, and I need to covert it into sqldatetime in the format of 'YYYY-MM-DD, 00:00:000', so that it can result correctly in SQL.

I tried the below, and it's not working. What did I do wrong?

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=AMSDataWarehouse     test;Integrated Security=SSPI"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandText = "YQBreport1"
SqlParameter param1 = new SqlParameter("@from",System.Data.SqlDbType.DateTime);
param1.Value = $from;
SqlParameter param2 = new SqlParameter("@to",System.Data.SqlDbType.DateTime);
param2.Value = $to;
$SqlConnection.Open()
$sqlcmd.ExecuteNonQuery()
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$SQLResult =$DataSet.Tables[0]
$commands = $SQLResult | foreach-object -process { $_.output }> output.ps1
.\output.ps1

Ansgar Wiechers · Accepted Answer · 2013-03-11 10:41:59Z

2

Your syntax isn't valid PowerShell:

SqlParameter param1 = new SqlParameter("@from",System.Data.SqlDbType.DateTime);
param1.Value = $from;

Try this:

$param1 = $SqlCmd.Parameters.Add("@from", [System.Data.SqlDbType]::DateTime)
$param1.Value = Get-Date $from
$param2 = $SqlCmd.Parameters.Add("@to", [System.Data.SqlDbType]::DateTime)
$param2.Value = Get-Date $to

answered Mar 11, 2013 at 10:41

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Recep Karabıçak Over a year ago

can add -format "yyyy-MM-dd" option to the Get-Date as an improvement

Apriljuly Over a year ago

New question, the value doesn't seems to be passed to stored procedure, is my connection string wrong?

Ansgar Wiechers Over a year ago

@Apriljuly I don't see anything inherently wrong with your connection string, but I'm not an SQL Server expert. You could try generating it at runtime. That should largely rule out the possibility of a malformed connection string. Other than that I could only suggest to check the connection state and also the SQL Server logs (you may need to increase the log level).

Joel Coehoorn Over a year ago

@solidrevolution That's not an improvement. Much better to use an actual datetime object over a string. If you ever find yourself writing code to format date strings for use in SQL, you're doing something very wrong.

Collectives™ on Stack Overflow

Convert string from PowerShell into sqldbtype. datetime

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related