I have my getdate() = '2022-03-21 09:24:34.313'

I'd like to build Start Month and End Month dates intervals with SQL language (SQL server) , with the following screen :

CodePudding user response：

You can use EOMONTH function and DATEADD function to get the data you want.

But, the best approach would be to use a calendar table and map it against the current date and get the data you want.

DECLARE @DATE DATE = getdate()

SELECT DATEADD(DAY,1,EOMONTH(@DATE,-1)) AS MonthM_Start, EOMONTH(@DATE) AS MonthM_End,
       DATEADD(DAY,1,EOMONTH(@DATE,-2)) AS MonthOneBack_Start, EOMONTH(@DATE,-1) AS MonthOneBack_End,
       DATEADD(DAY,1,EOMONTH(@DATE,-3)) AS MonthTwoBack_Start, EOMONTH(@DATE,-2) AS MonthTwoBack_End,
       DATEADD(DAY,1,EOMONTH(@DATE,-4)) AS MonthThreeBack_Start, EOMONTH(@DATE,-3) AS MonthThreeBack_End

CodePudding user response：

You can use a recursive CTE to avoid having to hard-code an expression for each month boundary you need, making it very easy to handle fewer or more months by just changing a parameter.

Do you really need the end date for processing? Seems more appropriate for a label, since date/time types can vary - meaning the last day of the month at midnight isn't very useful if you're trying to pull any data from after midnight on the last day of the month.

This also shows how to display the data for each month even if there isn't any data in the table for that month.

DECLARE @number_of_months int = 4,
  @today date = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

;WITH m(s) AS
(
  SELECT @today UNION ALL SELECT DATEADD(MONTH, -1, s) FROM m
  WHERE s > DATEADD(MONTH, 1-@number_of_months, @today)
)
SELECT MonthStart = m.s, MonthEnd = EOMONTH(m.s)--, other cols/aggs
FROM m
--LEFT OUTER JOIN dbo.SourceTable AS t
--ON t.datetime_column >= m
--AND t.datetime_column < DATEADD(MONTH, 1, m);

Output (without the join):

MonthStart	MonthEnd
2022-03-01	2022-03-31
2022-02-01	2022-02-28
2022-01-01	2022-01-31
2021-12-01	2021-12-31

• Example db<>fiddle

But, as mentioned in a comment, you could easily store this information in a calendar table, too, and just outer join to that:

SELECT c.TheFirstOfMonth, c.TheLastOfMonth --, other cols/aggs
  FROM dbo.CalendarTable AS c
  LEFT OUTER JOIN dbo.SourceTable AS t
  ON t.datetime_column >= c.TheFirstOfMonth
    AND t.datetime_column < c.TheFirstOfNextMonth
  WHERE c.FirstOfMonth >= DATEADD(MONTH, -4, GETDATE())
    AND c.FirstOfMonth < GETDATE();