SQL Statement Variable Date Range

[wasonj1]

Hi There,

I have a query that is using an SQL statement that is currently pulling data between 2018-07-01 and 2019-07-01. I want to update the SQL statement to be able to pull data between the current date and 2 years older without hardcoding the dates. Can someone tell me how to update my SQL statement to do this?

[danextian · Accepted solution]

Hi @wasonj1 ,

In Power Query, you can add a custom step to filter by an automatic date range:

= Table.SelectRows(#"Name of Previous Step", each [DateColumn] >= Date.AddYears(Date.From(DateTimeZone.LocalNow()), -2))

In SQL, you can use just filter by dates using the SQL syntax. I am not very familiar with SQL but the code should be something like this:

WHERE M.Response_Date >= DATEADD(YEAR, -2, GETDATE())

 or perhaps

WHERE M.Response_Date >= DATEADD(YEAR, -2, TODAY())

[dax · Accepted solution]

Hi @wasonj1 , 

You could try to refer to @danextian 's suggestions, or you also could refer to Pass-parameter-to-SQL-Queries-statement-using-Power-BI  for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian · Accepted solution]

Hi @wasonj1 ,

In Power Query, you can add a custom step to filter by an automatic date range:

= Table.SelectRows(#"Name of Previous Step", each [DateColumn] >= Date.AddYears(Date.From(DateTimeZone.LocalNow()), -2))

In SQL, you can use just filter by dates using the SQL syntax. I am not very familiar with SQL but the code should be something like this:

WHERE M.Response_Date >= DATEADD(YEAR, -2, GETDATE())

 or perhaps

WHERE M.Response_Date >= DATEADD(YEAR, -2, TODAY())