Updating date in Query daily

[Anonymous]

I have the following Query, which has pulled historical data from a database for the last 7 months into Power BI.

Query="SET NOCOUNT ON#(lf)DECLARE @StartDate DateTime#(lf)DECLARE @EndDate DateTime#(lf)SET @StartDate = '20210514 07:00:00.000'#(lf)SET @EndDate = '20211214 07:00:00.000'#(lf)SET NOCOUNT OFF#(lf)SELECT  * FROM (#(lf)SELECT History.TagName, DateTime, Value, vValue, StartDateTime#(lf) FROM History#(lf) WHERE History.TagName IN ('S03_FT03_04_TOT01')#(lf) AND wwRetrievalMode = 'Delta'#(lf) AND wwVersion = 'Latest'#(lf) AND DateTime >= @StartDate#(lf) AND DateTime <= @EndDate) temp WHERE temp.StartDateTime >= @StartDate"])

I need the Query to refresh every day at 7am and update the report with the historical values over the past day. Is there a functionality for this in Power BI? I am not familiar with SQL so not sure how to modify this Query to achieve this.

[smpa01]

@Anonymous  you can pass on a dynamic scalar value for the @EndDate

--EndDate=GETDATE()

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '20210514 07:00:00.000'
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE()))
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF

--EndDate=GETDATE()-1

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '20210514 07:00:00.000'
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE())-1)
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF

[Anonymous]

@smpa01 

Thanks smpa. Do you know how I can do the same for StartDate, i.e. set it as yesterday? I am not sure why you did GETDATE() -1 for the EndDate. Will this not set yesterday as the EndDate? 

[smpa01]

@Anonymous  if you are running the SQL query today, what should be the 

StartDate

and 

EndDate

[Anonymous]

@smpa01 

StartDate yesterday and enddate today, both at 7am. Will they both be the same but with getdate() and getdate()-1?

[smpa01 · Accepted solution]

@Anonymous  try this

SET NOCOUNT ON

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE())-1)
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE())-1)
					,' 07:00:00.000'
					))
		)
SET @EndDate = (
		SELECT CONVERT(DATETIME, CONCAT (
					CONVERT(VARCHAR(4), YEAR(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), MONTH(GETDATE()))
					,'-'
					,CONVERT(VARCHAR(2), DAY(GETDATE()))
					,' 07:00:00.000'
					))
		)
SET NOCOUNT OFF