Pass Date as Dynamic Query Parameters with SQL source

[BugmanJ]

Hello,
So my powerquery (using Direct) is:

let
    Source = Sql.Database("ServerName", "Database", [Query="SELECT * FROM TABLE.CAKE_ACTIVITY
    WHERE [ACTIVITY_DATE] >" &  datetime("SDate 'yyyy-MM-dd hh:mm'),")])
in
    Source

and Sdate is currently a Parameter which is "30/11/2022 13:10:00"

[Activity_Date] is currently stored in the SQL database as 2022-11-30 13:10:00"

Sadly it just sits and twirls where if did this in the SQL:

SELECT *
FROM
[TABLE].[CAKE_ACTIVITY]
WHERE [ACTIVITY_DATE] > '2022-11-30 13:10:00'

 it works just fine.

Whats my error?
Thank you

[ppm1]

You don't need a native query for your example. You can just add the filter step in the query editor and it should "fold" back to the source (right click on last step and "View Native Query" to confirm). However, you can also concatenat your parameter value in with syntax like this.

Sql.Database("localhost", "Flights", [Query="SELECT * FROM Date WHERE [Date] > convert(datetime2, '" & DateTime.ToText(Parameter1, "yyyy-MM-dd hh:mm:ss") & "')"])

Pat

[BugmanJ]

@ppm1  Thanks this works, but then sadly binding a parameter to it doesnt that make it do anything as seen here 

[BugmanJ]

@v-yalanwu-msft  No that doesnt work at all. It either thinks Sdate is a column or if correctly split, does not allow & to types Datetime

[v-yalanwu-msft]

Hi, @BugmanJ ;

You could try it.

let
    Source = Sql.Database("ServerName", "Database", [Query="SELECT * FROM TABLE.CAKE_ACTIVITY
    WHERE [ACTIVITY_DATE] >SDate])
in
    Source

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.