Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Wolfshade
Frequent Visitor

Passing Date Parameters to a Stored Procedure

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:

Invoke1.JPG

Pressing Invoke I get:

Invoke2.JPG

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)?

1 ACCEPTED 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:

 

Invoke3.JPG

Because of the date conversion it then changes the format that is passed to SQL

Invoke5.JPG

So then I get the right dates back

Invoke4.JPG

 

Though, to be honest it might just be easier to set my regional settings to the US 😕

View solution in original post

4 REPLIES 4
fmunar1985
Frequent Visitor

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!!!

fmunar1985_0-1631887271127.png

 

fmunar1985_1-1631887289911.png

 

fmunar1985_2-1631887312072.png

 

v-yuta-msft
Community Support
Community Support

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:

 

Invoke3.JPG

Because of the date conversion it then changes the format that is passed to SQL

Invoke5.JPG

So then I get the right dates back

Invoke4.JPG

 

Though, to be honest it might just be easier to set my regional settings to the US 😕

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.