How can you send a datetime to SQL Server using powershell?

Welcome Forums General PowerShell Q&A How can you send a datetime to SQL Server using powershell?

This topic contains 2 replies, has 2 voices, and was last updated by

 
Participant
1 month ago.

  • Author
    Posts
  • #131186

    Participant
    Points: -6
    Rank: Member

    I've not been able to find a working answer to this question anywhere-which is really weird if you ask me. Every attempt to pass the datetime to the database has failed with a conversion type string error.  The SQL Server i'm using has a column on a table that is of type "datetime" and an example value is

    2009-09-16 00:00:00.000

    My current script is:

    $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 adding [datetime] to the $nd-but again getting the same kind of failure. The database or powershell just doesn't like the execution.

  • #131198

    Participant
    Points: 192
    Helping Hand
    Rank: Participant

    Don't use quotes in the query when using parameters...

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

    The deal is that one of the reasons for using parameters is to make it injection safe, so you don't need to worry about quotes.

    • #131225

      Participant
      Points: -6
      Rank: Member

      Thank you sir-turns out that was all.....I could have sworn you needed them at some point before...

You must be logged in to reply to this topic.