sqlpowershellpowershell-4.0

Concatenation of variable not working with SQL command in PowerShell script


I am trying to get the value of a date variable into the string by concat

 $Lastextracteddate=Get-ChildItem $folder |
    Select-Object @{Name="LastWriteTime";
    Expression={$_.LastWriteTime.ToString("yyyyMMddHHmmss")}} | Select -LAST 1 | Format-Table -HideTableHeaders
    
    $queryresult="select count(1) from user_table  WHERE replace(convert(varchar,[CREATE_S],112),'/','') +
    replace(convert(varchar,[CREATE_S],108),':','')>'$Lastextracteddate'"
   echo $Lastextracteddate
   echo $queryresult

I tried to pass the variable as above. Also tried to pass without single quote but no luck. Getting result as below. Need help on this topic.

PS C:\Users> .\test.ps1

20231215191534

select count(1) from user_table  WHERE replace(convert(varchar,[CREATE_S],112),'/','') +
replace(convert(varchar,[CREATE_S],108),':','')>'Microsoft.PowerShell.Commands.Internal.Format.FormatStartData Microsoft.PowerShell.Commands.Internal.Format.GroupStartData Microsoft.PowerShell.Commands.Internal.Format.FormatEntryData Microsoft.PowerShell.Commands.Internal.Format.GroupEndData Microsoft.PowerShell.Commands.Internal.Format.FormatEndData'

Solution

  • In the following script, I eliminated the use of Format-Table (which outputs format data, not actual values) and directly extracted the last write time of the latest file in the desired format as a string, using Select-Object -Last 1 and ForEach-Object to format the date, ensuring $Lastextracteddate correctly stores the date string for use in the SQL query. Try this

    $Lastextracteddate = Get-ChildItem $folder |
        Select-Object -Last 1 |
        ForEach-Object { $_.LastWriteTime.ToString("yyyyMMddHHmmss") }
    
    $queryresult = "select count(1) from user_table WHERE replace(convert(varchar,[CREATE_S],112),'/','') + replace(convert(varchar,[CREATE_S],108),':','') > '$Lastextracteddate'"
    
    echo $Lastextracteddate
    echo $queryresult