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

Solution 1:

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") & "'"]