recursive start and end of month

 

DECLARE     @StartDate DATE = '20231215';

 

WITH cteSource(MonthSerial)

AS (

      SELECT      DATEDIFF(MONTH, '19990101', @StartDate) AS MonthSerial

 

      UNION ALL

 

      SELECT      MonthSerial - 1 AS MonthSerial

      FROM  cteSource

      WHERE MonthSerial > 0

)

SELECT      MONTH(DATEADD(MONTH, MonthSerial, '19990101')) as IMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990101')as DATE) AS STARTOFMONTH,

            CAST(DATEADD(MONTH, MonthSerial, '19990131')as DATE) AS ENDOFMONTH

FROM  cteSource

OPTION      (MAXRECURSION 0)