Pass Parameter from Excel Cell to SQL Query

[Shortmeister1]

Hi

I have a query in SQL, an excerpt of which might look something like

DECLARE @myDate DATETIME
SET @myDate = '2020-10-31'
SELECT * FROM myTable WHERE myDate <= @myDate + 1

I want to populate a cell in Excel (e.g. A1) named range "myCell" that will feed through to the SQL variable instead of setting it in the SQL code.

I've not really worked with parameters in Power Query before and a lot of searching has left me with solutions that are similar/cause syntax errors in the M code, and a great big headache.

Can someone point me in the right direction please?

Thanks

 

[smozgur]

We know that we can get that named cell value into Power Query like this:

myDate = Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1]

And then you just need the SQL SELECT statement, right? Perhaps following might help.
Assuming myCell contains a date value in Excel, I think following should be a sample to create the query, and use with Sql.Database function.

let
    myDate = Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1],
    nextDay = Date.AddDays(Date.From(myDate), 1),
    sqlDate = Date.ToText(nextDay, "yyyy-MM-dd"),

    Source = 
        Sql.Database(
            "server", 
            "database", 
            [Query="SELECT * FROM myTable WHERE myDate <= " & sqlDate])

in
    Source

 

[Shortmeister1]

Thanks Smozgur. (Love the BatCoder handle)

I was so close!

For various reasons that I won't bore you with, I am unable functions or procs on this database. Not in a reasonable timeframe anyway, hence my vary specific request. Procs would be my preferred option any day!

 

[Shortmeister1]

Alas, it still has an error. Power Query has automatically split it into three steps:

myDate

= Excel.CurrentWorkbook(){[Name="myCell"]}[Content]{0}[Column1]

sqlDate

= Date.ToText(myDate, "yyyy-MM-dd")

and the third is the actual SQL.

sqlDate fails. The error message is:

Expression.Error: We cannot convert the value #datetime(2020, 10, 31, 0, 0, 0) to type Date.
Details:
Value=31/10/2020 00:00:00
Type=Type

*By the way, I removed the nextDay step as this is covered in the main body of the SQL.

 

[Norie]

Have you tried using #date instead of #datetime?

 

[Shortmeister1]

Morning Norie - hope you're well and not coughing!

Maybe I'm being a bit thick, but I can't see where I specific #datetime in the code above?

 

[Norie]

Oops, sorry - I was looking at the error message from PowerQuery.

What format is your Excel date in?

 

[Shortmeister1]

It needs to be 'YYYY-MM-DD' - ie internal SQL date format. It then gets passed to a datetime parameter inside the SQL where the parameter is dealt with in different ways, dependent on the date.
(Not that I'm interested in explaining the workings of one extremely complex bit of SQL )

I'm finding it a bit difficult to believe that passing a date from an Excel cell to @AS_AT_DATE, which is the real date parameter in the SQL code, could be so not straightforward. I thought that Smozgur's solution would crack it, but there's still one small piece of the puzzle missing. I've found nothing on t'internet that explains how to do this.

 

[Norie]

What happens if you convert the date in Excel to a text value with the required format using TEXT.

The reason I suggest that is because it seems, to me anyway, that Power Query is seeing a time part in the value you are passing to it.

 

[smozgur]

By the way, I removed the nextDay step as this is covered in the main body of the SQL

You also removed Date.From that handles that error.
Use this for sqlDate instead:

sqlDate = Date.ToText(Date.From(myDate), "yyyy-MM-dd")