0

I am working on a PowerShell script to generate a HTML report from a SQL Server 2008 R2 query. The current script shows as:

$Date = Get-Date
$Summary = Invoke-SQLcmd -Query $Qry | Select-Object -Property 'Oldest Work Date'
$Summary | ForEach-Object {
  if (($Date - $var=$_.'Oldest Work Date').Days -gt 90)
  {
    {
      $data+=
       "<tr bgcolor = `"yellow`">
         <td>$($_.'Oldest Work Date')</td>
        </tr>"
    }
  else
    {
      $data+=
       "<tr>
         <td>$($_.'Oldest Work Date')</td>
        </tr>"
    }
  }

My goal is to markup the dates greater than 90 days ago with the row highlighted in yellow.

I am having trouble with the expression- 

(($Date - $var=$_.'Oldest Work Date').Days -gt 90)

I am receiving the error in SQLPS: Invalid assignment expression. The left hand side of an assignment operator needs to be something that can be assigned to a variable or a property.

I have searched and tried many different versions. Ie. prefixing the variables with [datetime], used New-TimeSpan, and also without the .Days. Please let me know if this is possible.

MLowry54
  • 13
  • 5
  • Your expression doesn't make much sense. What is `$var` doing in there? Did you mean `(($Date - $_.'Oldest Work Date').Days -gt 90)`? – Thomas Glaser Aug 25 '17 at 14:30
  • I tried your suggestion and received multiple errors: Method invocation failed b/c [System.String] doesn't contain a method name op_Subtraction. AND Bad numeric constants for my dates – MLowry54 Aug 25 '17 at 14:33
  • Then `$_.'Oldest Work Date'` is not a `DateTime`. You'll have to check what format the date is in and convert that. `[DateTime]::Parse($_.'Oldest Work Date')` might just work if it's in your regional format. – Thomas Glaser Aug 25 '17 at 14:37
  • you were correct @Raziel; I had converted the dates in my SQL query. – MLowry54 Aug 25 '17 at 16:20

1 Answers1

0
if ($var=([datetime](Get-Date) - [datetime]$_.'Oldest Work Date').Days -gt 90)
MLowry54
  • 13
  • 5