At the moment I'm using Powershell to run a SQL query on a SQL Server database and output the results to an Excel spreadsheet.
It currently outputs via
invoke-sqlcmd -inputfile "C:\scripts-and-reports\all_new-matters-since-last-run.sql" -serverinstance "PE-SERVER\PRACTICEEVOLVE" -database "PracticeEvolve_c1" |
Select-Object * -ExcludeProperty "RowError","RowState","Table","ItemArray","HasErrors" |
Export-XLSX -WorksheetName "15-05-2017 to $(get-date -f dd-MM-yyyy)" -Path "C:\scripts-and-reports\15-05-2017 to $(get-date -f yyyy-MM-dd) Taree PE new clients.xlsx" -Force -Verbose
which is working fine because I'm manually specifying the date filter in the SQL query, in the WorksheetName, and in the file name.
I aim to automate this entire process so it runs on the 1st of each month and reports on the entire previous month.
I've now since modified my SQL query to filter for the previous month, but I'd like to have the output file's worksheet and filename to just include the previous month's name (eg. "August" if I were running it today).
I've tried
invoke-sqlcmd -inputfile "C:\scripts-and-reports\all_new-matters-for-last-month.sql" -serverinstance "PE-SERVER\PRACTICEEVOLVE" -database "PracticeEvolve_c1" |
Select-Object * -ExcludeProperty "RowError","RowState","Table","ItemArray","HasErrors" |
Export-XLSX -WorksheetName "$(get-date AddMonths(-1) -f MMMM)" -Path "C:\scripts-and-reports\$(get-date AddMonths(-1) -f MMMM) Taree PE new clients.xlsx" -Force -Verbose
but it doesn't work. I get an error about the string not being a valid DateTime.
Where have I gone wrong and what should it be?