Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I am having difficulties passing dates from a parameter in Power BI Desktop to a stored procedure in SQL.
The SP I am trying to run is quite long, so I created a test one:
CREATE PROCEDURE DateParm @startdate date, @enddate date AS BEGIN SET NOCOUNT ON; SELECT @startdate StartDate, @enddate EndDate END
I then created a new data source in Desktop with hardcoded parameters:
EXEC DateParm @startdate = '2017-01-01', @enddate = '2017-12-31'
I then modified the code for in Advanced editor:
let SQLSource = (param1 as date, param2 as date) => let Source = Sql.Database([MyServer], [Test], [Query="EXEC dbo.DateParm @startdate = '"& Date.ToText(param1) & "', @enddate = '"& Date.ToText(param2) & "'"]) in Source in SQLSource
Where [MyServer] and [Test] are the actual server and database names where my Stored Procedure lies.
I then add my date values back in:
Pressing Invoke I get:
Running SQL Server Profile I can see that it is trying to run the following script:
EXEC dbo.DateParm @startdate = '01/01/2017', @enddate = '31/12/2017'
Which doesn't evaluate owing to the varchar to date conversion error.
However, if I change my regional settings to US then it evaluates fine.
Is there a way to pass the date without having my regional settings set to English (United States)?
Solved! Go to Solution.
So, I've got a solution. Though it is ugly.
The formatting does not ultimately impact what is being passed from Power BI to SQL. Consequently, I need to recombine the date string so that it is in a different format from when it is being created.
let SQLSource = (param1 as date, param2 as date) => let param1 = Date.ToText(param1, "MM")&"/"&Date.ToText(param1, "dd")&"/"&Date.ToText(param1, "yyyy"), param2 = Date.ToText(param2, "MM")&"/"&Date.ToText(param2, "dd")&"/"&Date.ToText(param2, "yyyy"), Source = Sql.Database([MyServer],[Test], [Query="EXEC dbo.DateParm @startdate = '"& param1 & "', @enddate = '"& param2 & "'"]) in Source in SQLSource
This then swaps the position in the string of the day and month parts of the date so that when they are passed they are in the USA format, making SQL happy again.
So I pass the 16th April 2018 and 22nd April 2018 to the function:
Because of the date conversion it then changes the format that is passed to SQL
So then I get the right dates back
Though, to be honest it might just be easier to set my regional settings to the US 😕
HELP!!!!
Hello i have a question:
I have a store in SQL with a Date parameter and adittional to that i have another query with that date i need to put that date in a FILTER and pass that date in the store so when i filter that date the store will be refresh.
Example:
let
SQLSource = (FechaStock as date) =>
let
Source = Sql.Database("Server", "DataBase", [Query="Exec Store @fechastock = '" & Date.ToText(FechaStock)&"'"])
in
Source
in
SQLSource
Please i need help with this :(, because i already put the connections between the query store and the Date Query in the model and oin ly brings me the default parameter.
THANKS!!!
Hi Wolfshade ,
You can add a format option and try again.
=Date.ToText([date],"dd/MM/yyyy")
Regards,
Jimmy Tao
Hi @v-yuta-msft,
Thanks for the response. I had tried this (and other formatting variations) before posting and unfortunately it doesn't work.
Profiler still shows that the parameters are being passed:
EXEC dbo.DateParm @startdate = '01/01/2017', @enddate = '31/12/2017'
Which the SQL then looks at in the format mm/dd/yyyy and so throws up the same error message:
Error converting data type varchar to date.
Regards,
So, I've got a solution. Though it is ugly.
The formatting does not ultimately impact what is being passed from Power BI to SQL. Consequently, I need to recombine the date string so that it is in a different format from when it is being created.
let SQLSource = (param1 as date, param2 as date) => let param1 = Date.ToText(param1, "MM")&"/"&Date.ToText(param1, "dd")&"/"&Date.ToText(param1, "yyyy"), param2 = Date.ToText(param2, "MM")&"/"&Date.ToText(param2, "dd")&"/"&Date.ToText(param2, "yyyy"), Source = Sql.Database([MyServer],[Test], [Query="EXEC dbo.DateParm @startdate = '"& param1 & "', @enddate = '"& param2 & "'"]) in Source in SQLSource
This then swaps the position in the string of the day and month parts of the date so that when they are passed they are in the USA format, making SQL happy again.
So I pass the 16th April 2018 and 22nd April 2018 to the function:
Because of the date conversion it then changes the format that is passed to SQL
So then I get the right dates back
Though, to be honest it might just be easier to set my regional settings to the US 😕
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |