Forum Discussion
Dynamic parameter for SQL to filter last year
Hi I need to pass a dynamic parameter to an SQL query, so I can get the last year, but filtered in the SQL code. Actually, I'm using a fixed parameter, with today's day and month. But how can I...
Finally, I've solved it in this way:
let //First, create the variable in PowerQuery Startdate=Text.From(Date.Year(Date.AddMonths(DateTime.LocalNow(),-12))), StartMonth=Text.PadStart(Text.From(Date.Month(Date.AddMonths(DateTime.LocalNow(),-12))),2,"0"), YearMonth=Startdate & StartMonth, //Then use the variable in the SQL Source = Sql.Database("ServerName", "DatabaseName", [Query=" Select * from TABLE_NAME Where YEAR_MONTH='"&YearMonth&"' "]) in Source
In theory you could use a query parameter and have it be the SQL code to evaluate. Then in your SQL query/stored procedure/etc. just wrap an EVALUATE around that parameter coming in?