Powershell DateTime to SQL DateTime

Question

I have a PowerShell script that reads in a date from a registry, then uses it in an SQL query

# get date from registry
$avdate = (readregistry -regpath "HKLM:\SOFTWARE\MCAFEE\agent" -targetval "InstallTime") -as [DateTime]
$avdate = $avdate.tostring("yyyy-MM-dd HH:mm:ss")

# query, ExecQuery() makes calls to the database, EscapeQuote() is for string formatting
$returnRS = ExecQuery("exec SystemCheckerVBS_UpdateComputer '" + (EscapeQuote($serialNumber)) + "','" + (EscapeQuote($computerName)) + "',3,'$hostip','$hostmac','$avdate','$ostype','$osbit','$spver','$sdc'")

#query function
function local:ExecQuery($sqlStr){
        #write-host "query: $sqlstr" #debug
    $SqlCmd.CommandText = $sqlStr
    $SqlAdapter.SelectCommand = $SqlCmd 
    $dataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    return $dataSet
}

when running the query it produces (Exception calling "Fill" with "1" argument(s): "Error converting data type varchar to datetime."). using the "yyyMMdd" format also does not work, neither does passing it directly as a datetime object. here is where the SQL procedure receives the data, only other uses in procedure are being passed into insert and update statements without alteration. no other variables are or deal with datetime.

@I_AntiVirusDate datetime = '2001-01-01 00:00:01'

is this a formatting issue, syntax, or possibly something else?

Hi. This is the type of question that requires a [Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example) to answer. From your existing post, we don't know what the data in the registry looks like, or what the stored procedure being called does. Please edit your question to include the relevant details, enough such that we can reproduce the problem and help you identify the cause. Thanks. — Matt Johnson-Pint, Jan 29 '20 at 21:29
Also, I'll point out that by embedding your parameters in strings, your code is vulnerable to SQL Injection attacks. You should add them as actual parameters on `$SqlAdapter.Parameters` instead. See https://bobby-tables.com/ — Matt Johnson-Pint, Jan 29 '20 at 21:32

score 0 · Answer 1 · answered Jan 29 '20 at 23:37

0

You may want to use one of these three formats

'2001-01-01T00:00:01'
CAST('2001-01-01 00:00:01' AS DateTime)
'2001-01-01'

answered Jan 29 '20 at 23:37

Mani Live

168
1
7

Powershell DateTime to SQL DateTime

1 Answers1