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)
