Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Passing Parameters to OleDB Datasource

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

Need Help
  • rubayatyasmin's avatar
    rubayatyasmin
    2 years ago

    hi, Anonymous 

     

    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

3 Replies

  • rubayatyasmin's avatar
    rubayatyasmin
    Community Champion
    2 years ago

    hi, Anonymous 

     

    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
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

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