Use the name of last month in PowerShell output file

Question

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?

Answer 1

get-date AddMonths(-1) -f MMMM 

This line is a mix of different syntaxes, and doesn't fit together.

Get-Date is a cmdlet which takes a parameter -Format which can be shortened to -f
Get-Date returns a [datetime] object

.AddMonths() is a method on a [datetime] object, not a parameter to Get-Date

Change to:

(Get-Date).AddMonths(-1).ToString('MMMM')

Which gets the date, then calls AddMonths on that return value, then uses the datetime way of converting that to text, because you can't use the -Format parameter here, because the cmdlet call finished earlier.