Selecting data between the fixed date every month in SQL dynamically-CodePudding

I was using the below sql query to pull report for the last month.

Select * from TABLE
where datediff(month, InvoiceDate, Getdate()) = 1

I link this SQL query to Excel Power Query to generate the monthly report automatically by just 1 refresh click.

Now my issue is that our reporting has changed from 26th of last month to 25th of the current month, so the above SQL query will not give me the correct report. Plus I have to add the date every month to generate the report.

Is there a way to add the date in where clause dynamically to generate the report every month, so that my tool work the way it was working earlier

CodePudding user response：

I have created a table and inserted values into that table.

Image for reference:

enter image description here

I fetched inserted values where invocedate is between 2022-09-01 and 2022-09-30 using below code.

declare @Startdate date=dateadd(MM, -1,getdate())
    
declare @Enddate date=dateadd(MM, -1,getdate())

declare @startingdate date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Startdate)-1),@Startdate))

declare @endingdate date = CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Enddate))),
DATEADD(mm,1,@Enddate)))

select * from report where invocedate between @startingdate AND @endingdate

Image for reference:

enter image description here