This is the function in SQL server to get the last day for each month until the day that you define in a variable which is in this case is “@genDate”.

I used this function to iterate some stored procedure every last day in the month

DECLARE @genDate datetime
SET @genDate = '1/31/2006'

WHILE @genDate <= getdate()
	BEGIN
		PRINT @genDate
		SET @genDate = DateAdd(month,1,@genDate)

		--this is used to get the last day for each month
		SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))
	END

NOTE: “-1” in this statement means that it’s minus one day from the beginning of the month which gives you the last day for each month
SET @genDate = DateAdd(day, -1, DateAdd(month, DateDiff(month, 0, @genDate)+1, 0))