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

     

ppgandhi111's avatar
ppgandhi111
Helper I
8 years ago
I am using SQL Server database. I have provided the query that I intend to use in my original post.
RahulYadav's avatar
RahulYadav
Resolver II
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