Passing Parameters to OleDB Datasource

[cwertz]

Hello,

I am looking to input a date parameter into the OleDB datasource code.

=OleDb.DataSource("provider=PIOLEDB.1;data source=osi-pi", [Query="SELECT *#(lf)FROM [piarchive]..[piavg]#(lf)WHERE tag = 'Value1' #(lf)AND timestep = '1h' #(lf)AND time BETWEEN'*-2y'AND'*'"])

I'd like to add the parameters StartDate and EndDate parameters into the *-2y and * part of the statement, respectively. 

Thanks

[rubayatyasmin · Accepted solution]

hi, @cwertz 

first define the start and end date pram then pass it like 

let
    StartDate = DateTime.ToText(DateTime.LocalNow() - #duration(730, 0, 0, 0), "yyyy-MM-ddTHH:mm:ss"), // 2 years ago from now
    EndDate = DateTime.ToText(DateTime.LocalNow(), "yyyy-MM-ddTHH:mm:ss"), // Current time
    Source = OleDb.DataSource("provider=PIOLEDB.1;data source=osi-pi", [Query="SELECT * FROM [piarchive]..[piavg] WHERE tag = 'Value1' AND timestep = '1h' AND time BETWEEN '" & StartDate & "' AND '" & EndDate & "'"])
in
    Source

[cwertz]

Thank you for your response. Is it possible to have the defined variables correspond to the parameters "TrialStart" and "TrialEnd." 

[rubayatyasmin]

you should be able to do it