Getting the last day for each month in SQL server

Fransiscus Setiawan

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))

AI engineer and solution architect in Sydney. Agents, MCP, RAG, Azure, .NET, EV charging. I write about what breaks after go-live.

Similar Posts