CODEWITHSUNDEEP
sql-serverpowerquerym
Stpete111
Stpete111

Reputation: 3457

Power Query data type conversion issue running SQL Server stored procedure

Latest version of Excel M365 and SQL Server 2017.

I have a stored procedure which takes a date as its input in format YYYY-MM-DD - currently the variable type is set to nvarchar(20), but I've also tried to resolve this issue by making it type Date.

I have a stored procedure that when run in T-SQL, looks like this:

EXECUTE [Database1].[dbo].[SP1]
@Date = '2020-10-01'

I'm using Power Query to pull the results of this into Excel. Here's what the generated M query looks like in Power Query:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = '2020-10-01'"])

This runs and imports the data as expected.

However now I'm trying to derive the @Date value from a cell in Excel. I have followed a good tutorial for making the date from the Excel cell available to Power Query and I believe I have that setup correctly. I have named the parameter SDate, and set it as type text since this is what the stored procedure is expecting.

So now the M query looks like this:

= Sql.Database("contoso.database.windows.net", "Database1", [Query="EXECUTE [Database1].[dbo].[SP1]#(lf)@Date = SDate"])

When I run this, I get this error in Power Query:

Microsoft SQL: Conversion failed when converting date and/or time from character string.

Two other things I tried:

One thing I found interesting is that after creating the aforementioned M query, if I go into settings of Source for the step that runs the query, the SQL Statement box shows this:

EXECUTE [Database1].[dbo].[SP1]
@Date = SDate

To me this looks like it's passing a literal SDate as the date to SQL Server, instead of the value of that parameter. I could be wrong there.

Any thoughts on getting this to run correctly?

Upvotes: 0

Views: 798

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40304

The literal string works if you drop it in right. You need to exit the literal text inside the quotes and append or insert the string you want.

Instead of this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = SDate"]

Try this:

[Query="EXECUTE [Database1].[dbo].[SP1] @Date = " & Sdate]

Where Sdate has been defined as the literal string '2020-10-01'.

If Sdate is a date, then use this:

[Query="EXECUTE [Database1].[dbo].[SP1]
@Date = '" & Date.ToText(Sdate, "yyyy-MM-dd") & "'"]

Upvotes: 1

Related Questions