I have the following query that imports data from SQL Server into Power BI.
SELECT *
FROM table_name
WHERE transaction_date > '2020-01-01 00:00:00'
I need to be able to change the value of the transaction_date
(a datetime value) without having to edit the query each time.
I attempted to do this using a parameter but I do not know how to add it properly to the SQL query. The parameter current_date
is a datetime value.
SELECT *
FROM table_name
WHERE transaction_date > '"¤t_date&"'
I get the error:
Conversion failed when converting date and/or time from character string.
Please tell me how to use the parameters, or if there is any other way to do this.
CodePudding user response:
You can convert the value to text using appropriate date format accepted by your database, for example this way:
DateTime.ToText(current_date, [Format="yyyy-MM-dd HH:mm:ss", Culture="en-US"])
And then use that value in your M code to modify the query:
let
Source = Sql.Database(".", "SomeDatabaseName", [Query="select *#(lf)from table_name#(lf)where transaction_date >= '" & DateTime.ToText(current_date, [Format="yyyy-MM-dd HH:mm:ss", Culture="en-US"]) & "'"])
in
Source
Note, that every time when the parameter value changes to something new, the user will be prompted to confirm the query. To turn this off, you must go to settings and uncheck Require user approval for native database queries
checkbox: