Forum Discussion
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...
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 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
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?