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
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'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