Forum Discussion

DukeCarey's avatar
DukeCarey
Regular Visitor
2 years ago
Solved

Pass date parameter to a SQL function

I need to pull data via a SQL function that requires a date parameter.  The SQL query is very simple: SELECT * FROM dbo.FunctionName('20230630').     All the search results I've found focus on usin...
Need Help
  • Fowmy's avatar
    Fowmy
    2 years ago

    DukeCarey 

    I created a parameter, called startdate, in your case it is linked to an Excel sheet, it;s fine. Here is the modified code, which should work for as well. Modify the Date format as per your source system.

    let
    DateParam = "'" & Date.ToText(StartDate, "yyyy-MM-dd") & "'",
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase(" & DateParam & ")", CreateNavigationProperties=false])
in
    Source

      

  • Fowmy's avatar
    Fowmy
    2 years ago

    DukeCarey 

    As there are two data sources talking to each other, the privacy level needs to be set, you may set it to None in this case: Go to Options and Settings then set it up for each source.

     

     

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
2 years ago

DukeCarey 
When you connect using the SQL as the source, your query should look like this. I created a function with a date parameter and and it works:

let
    Source = Sql.Database(".", "ContosoRetailDW_2019", [Query="SELECT * FROM dbo.GetCustomersByDateFirstPurchase('2004-03-14')", CreateNavigationProperties=false])
in
    Source