Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Updating date in Query daily

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 DateT...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    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
smpa01's avatar
smpa01
Community Champion
4 years ago

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

StartDate

and 

EndDate

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

smpa01 

 

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

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    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