How to get the the date range of previous month or quarter for reporting in SQL Server down to the s-CodePudding

We are creating a SQL report query for customers' SQL for sending out the generated custom SQL reports monthly, quarterly, or bi-annually as per the customer's agreements.

So need to calculate the date range dynamically when the SQL report is run for variables @startDate and @endDate up to the accuracy of the last second eg:

For previous month's report:

@startDate: 2021-11-01 00:00:00.000
@endDate: 2021-11-30 23:59:59.000

For previous quarter's report:

@startDate: 2021-07-01 00:00:00.000
@endDate: 2021-09-30 23:59:59.000

CodePudding user response：

Instead of calculating the last instant of a period, just use half-open intervals. eg

@startDate: 2021-07-01 
@endDate: 2021-10-01

then

select . . .
where Dt >= @startDate
  and Dt < @endDate

select . . .
where Dt >= @startDate
  and Dt < dateadd(month,3,@startDate)

etc.

CodePudding user response：

So finally after trying a lot of SQL query patterns including Calendar Tables, these simple queries seem to work best:

-- First and Last Day of last month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0) AS 'Last month start date'
    ,DATEADD(ss, - 1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) AS 'End Date';

GO

-- First and Last Day of Quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) -1, 0) AS 'Last Quarter start datetime'
, DATEADD(second, -1, DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)) AS 'Last Quarter start datetime';