Return First and Last day of each month in SQL Server

Hi Nithin093,

Refer below query.

SQL

CREATE TABLE #MonthDetails(Date DATE)

DECLARE @i INT,@Year INT,@Date DATE
SET @i = 0
SET @Year = 2020
SET @Date =CONCAT(@Year, '-02-01')

WHILE @i < 12
BEGIN
	DECLARE @First DATE,@Last DATE
	SELECT @First = DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date )-1, 0) --FIRST DAY OF PREVIOUS MONTH
	SELECT @Last = DATEADD(MONTH, DATEDIFF(MONTH, -1, @Date )-1, -1) --LAST DAY OF PREVIOUS MONTH
    INSERT INTO #MonthDetails VALUES (@First)
	INSERT INTO #MonthDetails VALUES (@Last)
	SET @Date = DATEADD(MONTH,1,@Date)
    SET @i = @i + 1
END

SELECT * FROM #MonthDetails
DROP TABLE #MonthDetails