Forum Discussion

ppgandhi11's avatar
ppgandhi11
Helper V
8 years ago
Solved

How to compute Date from Input Parameter?

Hi, I have a unique situation.   I have set up a input parameter for user for start date and end date. I have set them up as a text. It works okay.   My query is: select * from customers a where...
Need Help
  • RahulYadav's avatar
    RahulYadav
    8 years ago

    HI ppgandhi111,

    You can use Date.AddMonths function to go back 3 months from the StartDate Parameter.

     

    Could you please try to build similar Power Query in Advanced editor in Edit Query.

     

     

    let
    sDate=DateTime.ToText(Date.AddMonths(DateTime.FromText(StartDate),-3),"MM/dd/yyyy"), 
    eDate=EndDate,
    Source = Sql.Database("SERVERNAME", "DATABASE_NAME", [Query="select * from customers a where a.receiveddt between '" & sDate & "' and '" & eDate & "'"]),
    dbo_customers = Source{[Schema="dbo",Item="customers"]}[Data]
in
    dbo_customers

    Please change "SERVERNAME" & "DATABASE_NAME" as per your instance configuration.

     

    Below line in above code goes back 3 months from the given date parameter (StartDate).

    sDate=DateTime.ToText(Date.AddMonths(DateTime.FromText(StartDate),-3),"MM/dd/yyyy")

     

    Please let me know if this helps.

     

    Thanks,

    Rahul

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Click on Home > Edit Queries to see the result.  Download the file from here.

 

Hope this helps.

ppgandhi111's avatar
ppgandhi111
Helper I
8 years ago
Hi ashish, thanks.
As I mentioned in previous post I am not looking for running dates. I am looking for only manipulated parameter in the query for fetching data from the dB. The idea is to fetch data for previous quarter as well along with the requested data.. hope this helps. Thanks. Prashant-